# Count formula for Date Range

Options
✭✭

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?

• ✭✭✭✭✭✭
Options

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.

Regards,

Jeff Reisman

If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

• ✭✭✭✭✭✭
Options

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.

Regards,

Jeff Reisman

If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

• ✭✭
Options

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.

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!