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



Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!