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
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 63.8K Get Help
 406 Global Discussions
 219 Industry Talk
 457 Announcements
 4.7K Ideas & Feature Requests
 141 Brandfolder
 136 Just for fun
 57 Community Job Board
 459 Show & Tell
 31 Member Spotlight
 1 SmartStories
 297 Events
 37 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!