# Formula to return results based on workweek not calendar week

Options

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

Tags:

• ✭✭✭✭✭✭
edited 05/29/24
Options

Try this:

You may find this link useful:

Will this work for you?
Kelly

• Options

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

• ✭✭✭✭✭✭
Options

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

Kelly

• Options

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

• Options

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

• ✭✭✭✭✭✭
Options

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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!