The Workday function in Excel allows you to determine the date of the next workday given weekends and public holidays.
- For updated video clips in structured Excel courses with practical example files, have a look at our MS Excel online training courses . You can even try the Free MS Excel tips and tricks course.
- To see if this video matches your skill level (see the suggested skill score below) do our free MS Excel skills assessment.
- If you are based in South Africa look at the live courses we offer in Johannesburg and Cape Town.
Workday Excel Function
On the worksheet you can see a list of dates and we want to work out when the next workday will be based on those dates. Activate the function wizard – it must be in Date and Time and find the WORKDAY Excel function. Click OK. The first thing you get asked is what is the Start_ date – we want to look at 1st Jan 09 so enter cell A9. The next option is “Days” – this is the number of non-weekend and non-holiday days before or after the Start_date.
So I am asking Excel from the 1st Jan (I am going to put a 1 in) – tell me what the next workday is. What is also quite useful and is optional is you can specify when the public holidays are. You will see a list of public holidays on the worksheet in Column I. Highlight them to show Excel where they are – you must put in absolute references because you are going to copy it down. So in Holidays insert $I$9:$I$11. When I say OK it immediately tells me that the first working day after the 1st Jan is the 2nd Jan and also tells me it is a Friday. I can now copy it down for the whole month of January. When you now look it you’ll see for the 2nd, 3rd and 4th Jan – the next working day is the 5th which is a Monday. If you look at the 15th (a Thursday) Excel has identified this as a public holiday- so the next working day after the 14th (a Wednesday) is the 16th which is a Friday. So this function allows you to determine when the next workday will be.