Count formula for Date Range
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?
Best Answer

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 (1365)
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 MondaySunday and the past MondaySunday. Convert those back into the dates for the same and then create your COUNTIFS based on those date ranges.
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
Answers

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 (1365)
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 MondaySunday and the past MondaySunday. Convert those back into the dates for the same and then create your COUNTIFS based on those date ranges.
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

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
Categories
Check out the Formula Handbook template!