# 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 https://docs.google.com/spreadsheets/d/1nkwh1F3_3_bFdakkijjjRcKMUHJUOJ22QJKOWjzycnM/edit?usp=sharing ## 2 Answers 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 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? – marikamitsos Jan 28 at 16:59