Round to the nearest work day in Excel
Use this basic formula to round any scheduled date to the next working day (Monday to Friday) for use in workplace task planning.
In order to schedule tasks within the workplace, I have set-up an Excel worksheet which calculates when the first step of a process is commenced and then calculates when the next six steps would likely need to be completed. The starting day can vary, therefore tasks may be scheduled for weekends when no-one is at work.
To get around that, we can utilise Microsoft Excel’s
WEEKDAY function (which works in Excel 2007 and Excel 2010) and combine it with an
WEEKDAY returns the day of the week corresponding to a date. The day is given as an integer, ranging from 1 (Sunday) to 7 (Saturday), by default.
Let’s assume that we have a date value in cell A1. The following formula can be used to reference that cell and if the date falls on a Saturday or Sunday, rounds it forward to the next working day (a Monday):
The direct reference to cell A1 can be replaced with a
SUM function or any other equation that’s necessary to generate a desired date value.