Count formula for Date Range

JTodd

I need a formula to count the number of records where the Created On field is in current week Monday to Sunday and previous week Monday to Sunday.

I think this is just doing the last 7 days and dont take into account the day of the week.

=COUNTIFS({SubmittedOn}, AND(@cell >= (TODAY() - WEEKDAY(TODAY()) - 6), @cell < (TODAY() - WEEKDAY(TODAY()) + 1)))

Any suggestions?

Jeff Reisman

This is quite a doozy. You probably need some helper columns or even helper sheets to do some of the calculations/lookups.

Some pieces that could be helpful:

The YEARDAY function to determine the exact day of the year (1-365)

The WEEKDAY function

The WEEKNUMBER function

Use a helper sheet, and based off the value of TODAY(0), find the YEARDAY values for the current Monday-Sunday and the past Monday-Sunday. Convert those back into the dates for the same and then create your COUNTIFS based on those date ranges.

Jeff Reisman

This is quite a doozy. You probably need some helper columns or even helper sheets to do some of the calculations/lookups.

Some pieces that could be helpful:

The YEARDAY function to determine the exact day of the year (1-365)

The WEEKDAY function

The WEEKNUMBER function

Use a helper sheet, and based off the value of TODAY(0), find the YEARDAY values for the current Monday-Sunday and the past Monday-Sunday. Convert those back into the dates for the same and then create your COUNTIFS based on those date ranges.

JTodd

Thank you for the feedback. I am currently on site with a client. I will test this out on Thursday and let you know the outcome.