Date range formula

I have questions

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.

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.