Skip to content

Round to the nearest work day in Excel

G31st July 2014

CNo Comments

TExcel, Microsoft Office

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:

No comments have yet been submitted. Be the first!

Have your say:

The following HTML is permitted:
<a href="" title=""> <b> <blockquote cite=""> <code> <em> <i> <q cite=""> <strike> <strong>

Comments will be published subject to the Editorial Policy.