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 TuesdayFriday, but on Monday it is trying to return results for Sunday which are not accounted for as we only work MondayFriday. 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
Check out the Formula Handbook template!