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

  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭
    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

  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭
    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. :)

  • Success! Thank you!


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


    Worked!

  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭

    Awesome! Glad we could help you out!

  • sholmes
    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
    Mike Wilday ✭✭✭✭✭✭

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

  • Mike Wilday
    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.


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!