# Extract the Day of the month (dd) from a date in Query()

I have a list of transactions for a month, all include a date (Column B - dd/mm/yyyy) and a £in figure (Column F). I want to create a table that shows the total taken on each day of the month so for example on the April sheet, calculate the sum of all sales on the 1st, 2nd, 3rd

=SUM(QUERY(\$A\$3:\$F, "select F where 'TEXT(B,dd)' = 1",0))

=SUM(QUERY(\$A\$3:\$F, "select F where 'TEXT(B,dd)' = 2",0))

=SUM(QUERY(\$A\$3:\$F, "select F where 'TEXT(B,dd)' = 3",0))

This completes the query successfully but returns no results, despite there being transactions on these days.

Thanks

It would also be ideal if there was a way to input the formula so it can be filled into each row upto 31, without changing the date on each row

sample sheet with relevant data

• Welcome. Please share the link to a test sheet Jan 26, 2020 at 15:55
– Mike
Jan 26, 2020 at 19:06
• Please remember that as per site guidelines when an answer addresses your question, you should accept it so others can benefit as well. Jan 26, 2020 at 19:15

To extract the day of the month by using `QUERY`, use `day()`

Example:

``````=QUERY(\$A3:\$F,"select F where day(B) = 1")
``````

Reference

What you need is a query formula:

``````=query(A1:B, "select A, sum(B) where A is not null group by A order by A asc label sum(B) 'Takings'",1)
``````

You can then format your cells to show just the day of the date or whatever you like by going to:
`Format` >> `Number` >> `More formats` >> `More date and time formats`

Functions used:

• Thanks, This is very close to what I need, but I want to see the sum for every day, even if there are no transactions
– Mike
Jan 26, 2020 at 19:55
• "It would also be ideal if there was a way to input the formula so it can be filled into each row up to 31, without changing the date on each row". Have you solved your issue. Was `day(B) = 1` enough? Jan 28, 2020 at 16:59