Formula to return results based on workweek not calendar week
I have this formula =COUNTIFS([EOD received]:[EOD received], "no", [Attendance for]:[Attendance for], (TODAY(-1)))
It tells me how many end of day (EOD) emails have not been received by an employee for the previous day. So on Monday I check the attendance for the previous Friday, on Tuesday I check the attendance for Monday, etc. The formula looks at how many 'no' answers there are in the 'EOD received' column for the date in the 'Attendance for' column and returns the result.
The formula works great for Tuesday-Friday, but on Monday it is trying to return results for Sunday which are not accounted for as we only work Monday-Friday. I do know that I can change the -1 to a -3 so that on Monday it will return the result for Friday but that requires me to change the formula every Monday and then correct it on Tuesday. I also went with the option of just creating another sheet summary field just for the Monday formula with the -3 but again that is not the ideal solution.
Instead of having to edit the formula or having two separate sheet summary fields is there a formula that will provide me results on a Monday for the previous Friday as well as then provide the results on Tuesday for Monday etc? **The screenshot shows the formula I use on Monday's with the -3
Answers
-
Hey @VictoriaProctor
Try this:
=COUNTIFS([EOD received]:[EOD received], "no", [Attendance for]:[Attendance for], @cell = WORKDAY(TODAY(),-1))
You may find this link useful:
Will this work for you?
Kelly -
I will try and let you know! Thank you so much
-
Also note in the linked document above that the function allows you to add holidays, if desired
Kelly
-
@Kelly Moore Awesome! Thank you again - I will be trying that out shortly :)
-
@Kelly Moore - - Ummmm how do I test this today if the formula is looking at WORKDAY(TODAY(),-1) which would mean it's looking at yesterday's results.
-
Hey @VictoriaProctor
Change the -1 (for the test) to a larger negative number that would force the date to land on Monday or the weekend.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 66K Get Help
- 429 Global Discussions
- 149 Industry Talk
- 488 Announcements
- 5.2K Ideas & Feature Requests
- 85 Brandfolder
- 152 Just for fun
- 74 Community Job Board
- 499 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 305 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!