Formula to return results based on workweek not calendar week

VictoriaProctor

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

Kelly Moore

Hey @VictoriaProctor

Try this:

=COUNTIFS([EOD received]:[EOD received], "no", [Attendance for]:[Attendance for], @cell = WORKDAY(TODAY(),-1))

You may find this link useful:

https://help.smartsheet.com/function/workday

Will this work for you?
Kelly

VictoriaProctor

https://community.smartsheet.com/discussion/comment/438053#Comment_438053

I will try and let you know! Thank you so much

Kelly Moore

@VictoriaProctor

Also note in the linked document above that the function allows you to add holidays, if desired

Kelly

VictoriaProctor

@Kelly Moore Awesome! Thank you again - I will be trying that out shortly :)

VictoriaProctor

@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.

Kelly Moore

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.