Count days in odd workweek for different shifts

Options

Hello -

I currently am calculating "days safe" for each department and shift. Days safe is currently calculating "todays date" - "date of last accident". However, some shifts only work 4 days a week and other shift work 3 days a week.

Anyone have any suggested formulas for how I can designate number of work days per week for the calculation?

For example, say that "today" is "5-1-24. Date of last accident was Thursday 4-25-24. Current method shows "6 days safe"; counting Friday 26th, Saturday 27th, Sunday, 28th, Monday, 29th, Tuesday, 30th and Wednesday (1st). However, this shift only works M-Th. Therefore, "days safe" should return "3"; Monday, 29th, Tuesday, 30th, and Wednesday (1st).

I have other shifts that only work 3 days per week (Friday, Saturday, and Sunday) for which I also need to calculate "days safe".

Thanks in advance!

Answers

  • bisaacs
    bisaacs ✭✭✭✭✭
    Options

    Hey @ctsammon,

    You can use NETWORKDAYS() to return the number of working days between two dates. Also, you can change what is defined as a working day in Smartsheet if you go to Gantt view, then click on the gearbox in the upper right corner of the screen, enable dependencies, then click "Edit", which will allow you to define the days of the week that are working days:

    Because the shifts have different definitions of working days, I think it'd be really tricky to keep track of this on one sheet, and it might be easier to have separate sheets for the different shifts with different working days.

    Hope this helps!

    If my response was helpful in any way (or answered your question) please be sure to upvote it, mark it as awesome, or mark it as the accepted answer!

    I'm always looking to connect with other industry professionals, feel free to connect with me on LinkedIn as well!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!