When calculating with dates in Excel, there is no guarantee that the result will be on a working day. However, using the WORKDAY function offers a solution to rounding up or down to the nearest working day, it even allows for public holidays if required.

One of the topics on our Excel Intermediate course is calculating with dates. This blog post came about due to a question on one of those courses. If you need to know more about dates in Excel why not attend the course yourself, it’s our most popular IT course ever!

Background

Not all months of the year have an equal number of days, that’s a fact! Using EDATE allows us to work in months, and with a bit of maths in years too, as there are always 12 months in a year.

In the example below we are using EDATE to add 3 months to a project start date to calculate a review meeting date. However, there is no guarantee that it will fall on a working day.

WORKDAY function

To solve the problem, we can use a function called WORKDAY which adds a number of working days to a date and will always return a working date. Although we don’t actually want to add a number of working days to a date, we can still use it to return the nearest working day as in the following example.

Round up to the nearest working day

Subtract 1 from the date and then add it on again with the WORKDAY function. Cell C2 contains =WORKDAY(B2-1,1).

Round down to the nearest working day

Add 1 to the date and then subtract it again with the WORKDAY function. Cell D2 contains =WORKDAY(B2+1,-1).

Combining WORKDAY with EDATE

As we don’t need to see the 3 Month Review column, we would normally include the EDATE function within the WORKDAY function. For example, in C2 we could have =WORKDAY(EDATE(A2,3)-1,1).

WORKDAY even has an optional argument to allow for bank holidays and other non-working days, although I’ve not included it here.

IF and WEEKDAY functions

This was my initial solution, it’s more complex but also more flexible so I thought I’d include it for that reason.

Using the same dates from Column A in the above screenshot, I could have the following formula to round down to the previous working day.
=IF(WEEKDAY(EDATE(A2,3))=7,EDATE(A2,3)-1,IF(WEEKDAY(EDATE(A2,3))=1,EDATE(A2,3)-2,EDATE(A2,3)))

Explanation

• WEEKDAY returns 7 for Saturday and 1 for Sunday.
• If EDATE returns a date that falls on a Saturday then subtract 1 (previous day will be a Friday).
• If EDATE returns a date that falls on a Sunday, then subtract 2 (also returns the previous Friday).
• If EDATE returns a date that is not a Saturday or Sunday, then it’s already a working day so just return it as is.

Variations

• To round up, simply add 2 for a Saturday and add 1 for a Sunday.
• To round to the nearest working day, then subtract 1 for a Saturday and add 1 for a Sunday.

If you plan to use the calculation regularly, then why not create a custom function, no code required! Afterwards, instead of the previous formula, it might look something like this.

=3MonthReview(A2)

That will be the topic for another blog post, I think this one is long enough already.

Hope it’s helpful.

Thanks,