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 IF statement. 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):
=IF(WEEKDAY(A1)=7,A1+2,IF(WEEKDAY(A1)=1,A1+1,A1))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.
Comments
3 responses to “Round to the nearest work day in Excel”
8 years later and this is your first response, I hope you are going to get this inquiry. Thank you for this function, as it resolves part of my problem, however, I need to also factor in holidays and if the calculated date falls on a holiday, then it needs to calculate the next non-holiday work day. I can’t use the workday function, because I need to count all days including weekends and holidays. HOWEVER, if the calculated date ends up being a holiday or a weekend, then it must calculate the next non-holiday workday.
You can use WORKDAY with another nested function to account for holidays. The key is that once you get the target date, you have to subtract one and then have WORKDAY add one. I have a function like this: WORKDAY(EDATE(etc)-1,1, [holidays]). I was trying to figure out if it was possible to do without the -1 part, which is how I ended up here.
Hi Bryan,
Did you find solution to calculate the next non-holiday workday date?