﻿ Round to the nearest work day in Excel | Adam Dimech's Coding Blog

Dear Internet Explorer user: Your browser is no longer supported

Please switch to a modern browser such as Microsoft Edge, Mozilla Firefox or Google Chrome to view this website's content.

# 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.

<a href="" title=""> <b> <blockquote cite=""> <code> <em> <i> <q cite=""> <strike> <strong>