Using Dates for Summary
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.
Best Answer
-
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. :)
Answers
-
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. :)
-
Success! Thank you!
=COUNTIFS({Cost Center}, "X, {StartDate}, >=DATE(2020, 8, 1) <= DATE(2020, 8, 31))
Worked!
-
Awesome! Glad we could help you out!
-
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?
-
Question. Where are you putting that formula? What kind of column type, and what is the result you're getting?
-
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.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.6K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 63 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!