Excel: Calculating business days

For my job, I have a need to project end dates for temporary staff who cannot exceed a certain number of hours within a 12-month period. Figuring out how long 850 hours will last for employees who works Monday-Friday can be tedious, but Excel makes it simple.

The function =WORKDAY() returns which work day occurs after the number of days you input.

For instance, say I hired a staff member Jones who starts today. He may work up to 850 hours. I plan to have him work 8-hour shifts, five days a week. He will not work weekends, or holidays. When should I anticipate his last day to be?

First, Excel deals in days, not hours. So we’ll simply divide the 850 hours by 8 to know we have 106.25 work days. I don’t want to consider partial days, so we’ll use the =ROUNDDOWN() function to round that down to 106 days. Using the =WORKDAY() function, I can calculate the date that is 106 work days away from today. I use the $ symbol to indicate constant values so that every cell looks at the same one (B10) for today’s date. I also want to exclude 4 holidays in the calculation since Jones won’t work 8 hours on those days so I list those in G5:G8. Finally, I will include a logic formula in Column F to indicate whether or not the hours will be depleted before the anniversary date resets them. This would allow me to sort and only view employees where this is TRUE.

This is our result:

Screen Shot 2015-11-07 at 9.53.09 AM

Extra Credit

Microsoft has other recommended uses for this function too: Use WORKDAY to exclude weekends or holidays when you calculate invoice due dates, expected delivery times, or the number of days of work performed.