Using Dates for Summary

Aimee Peyton-Greene

Is there a way to use a date (like 08/1/2020- 08/31/2020, etc.) to a total count on a destination sheet?

I have a source sheet with the data I want to connect to a report that will give Monthly totals for multiple hire types (hires, terms, and contract) and by job code.

I can provide more information if needed.

Mike Wilday

You can use a greater than symbol and less than symbol to signify a date range.

=countifs([Date Column Name]:[Date Column Name], >=Date(2020,08,01),[Date Column Name]:[Date Column Name], <=Date(2020,08,31)) 

That formula would count the dates that are greater than or equal to 8/1/2020 and less than or equal to 8/31/2020. Just make sure to replace "Date Column Name" with your actual date column and remove the brackets if your header is 1 word and doesn't end in a number. :)

Mike Wilday

You can use a greater than symbol and less than symbol to signify a date range.

=countifs([Date Column Name]:[Date Column Name], >=Date(2020,08,01),[Date Column Name]:[Date Column Name], <=Date(2020,08,31)) 

That formula would count the dates that are greater than or equal to 8/1/2020 and less than or equal to 8/31/2020. Just make sure to replace "Date Column Name" with your actual date column and remove the brackets if your header is 1 word and doesn't end in a number. :)

Aimee Peyton-Greene

Success! Thank you!

=COUNTIFS({Cost Center}, "X, {StartDate}, >=DATE(2020, 8, 1) <= DATE(2020, 8, 31))

Worked!

Mike Wilday

Awesome! Glad we could help you out!

sholmes

Hey @Mike Wilday , I am trying to do something similar but it's not working. I want to get a total number of the items received by a certain date. Right now I have the formula below:

=countifs([JJJ. Final Tax Return Received Date]:[ JJJ. Final Tax Return Received Date], >Date(2023,12,01),[ JJJ. Final Tax Return Received Date]:[ JJJ. Final Tax Return Received Date], <=Date(2024,04,30)) 

I want to know the total received by April 30, 2024 the start date doesn't matter, I just need to know the number by the April date. Is that possible?

Mike Wilday

Question. Where are you putting that formula? What kind of column type, and what is the result you're getting?

Mike Wilday

Here is a working sample of the query. There are only 2 dates that qualify. I'm looking at dates or or after 1/1/2024 and before or on April 1, 2024. It's really important that your formula is in a text/column type.