Date range formula

Hello,


How would i write a formula when looking for data between a certain period i.e. I want to find the number of tasks marked 'complete' between March 1 through March 31? Thanks.

Answers

  • Christian Graf
    Christian Graf ✭✭✭✭✭

    Hello,

    You will want to make a helper column called month that has the following formula:

    =MONTH(Date@row)


    Then you can use this formula in your count column:

    =COUNTIFS([Month]:[Month], =3, [Task]:[Task], ="complete")

    That should give you the count you want.


    If you would like a more robust formula, you could do this:

    =IF(DAY(Date@row) =1, =COUNTIFS([Month]:[Month], =Month@row, [Task]:[Task], ="complete"),"")

    This formula only works if you have a beginning day of 1 for each month in your data. It checks if the date is the first day, then if it is, it will count all tasks that are complete and within that month.

    Hope this helps.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!