The WORKDAY function returns a date in the future or past that takes into account weekends and, optionally, holidays. You can use the WORKDAY function to calculate things like ship dates, delivery dates, and completion dates that need to take into account working and non-working days. Now, to review, date calculations can be really simple. To add one day to the date in B5, I can put “1” in C5, and then just use the formula: =B5 + C5 And this gives us December 23rd, as expected. If I change C5 to “-1” we get December 21st. This works because Excel stores all dates as serial numbers. But what happens when you need to skip weekends or holidays? Well, that’s what the WORKDAY function is for. Let me first apply a Custom number format to these dates, so we can easily see the day of the week. Custom format: ddd dd-mmm-yyyy The WORKDAY function takes three arguments: a start date, days, and an optional range for “holidays.” To skip weekends, I just need to give WORKDAY the start date from column B, and the day value from column C. =WORKDAY(B6,C6) With the value of one day, the result is December 25th, a Monday. WORKDAY skips the weekend. If I change days to “2” we get December 26th. Now, December 25th is Christmas, so it wouldn’t normally be treated as a workday. To take holidays into account, we need to provide a range of dates that represent non-working days. When I add that range as the 3rd argument, you can see that WORKDAY excludes those dates from the calculation. =WORKDAY(B7,C7,G5:G6) Finally, what if you need to treat a weekend day, like Saturday, as a workday? In that case, you’ll want to switch to the WORKDAY.INTL function which allows you to customize weekends. WORKDAY.INTL takes another optional argument called “weekend” which goes into the 3rd position, followed by holidays. For example, if Saturdays are normally workdays, you would supply the code “11” for weekend. =WORKDAY.INTL(B8,C8,11,G5:G6) And WORKDAY.INTL will treat Saturdays as workdays. A more flexible way to customize weekends is to use a special mask based on a text string of seven “1’s” and “0’s”, where “1’s” represent non-working days in a week that begins on Monday. So, for example, if Saturdays are normally a working day, I’d use six “0’s” followed by “1”. =WORKDAY.INTL(B8,C8,“0000001”,G5:G6) This scheme lets you specify any combination of working and non-working days in a 7-day week.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
