Have you ever wanted to put a schedule together in Excel with planned dates for something over a period of time, and then manually reviewed the formula, to change dates so they didn't fall on a holiday?
A client of mine prepares a billing schedule a year in advance where they have to incorporate certain specific holding periods and factor in different holidays of both themselves and their service providers (mostly the same holidays but some different). For example, their data is read by a third party service and it takes so many business days after a scheduled read date to get the data back for billing; then they have a window of so many business days to finish and post the billing cycle etc. All these dates are based on a start date and a factor of so many days - business working days - but excluding holidays. Here's a cool formula to help by incorporating holidays into date formulas. My example is simple: a deadline date to get data to the billing department, then they have 4 days to finish billing that month and the due dates are 18 days after billing. The billing date must be a weekday and not a holiday and the company also wants the due date of the bills to be a business working day.
First, a screen shot of my "billing schedule" mockup. I've included the day of the week in my date format just to show more clearly that the Bill Date and Due Date fields are not weekends, in this case.
The formula is shown below and is in the following format: WORKDAY(,,).
In my case:
- The start date on the Bill Date formula is the "deadline for input" date
- For the number of days, I used a cell reference (where you see the "4") so it's easily changeable
- The list of holidays is a range of cells off to the right (but it could be a hidden tab).
The number of days is + or - that you want to increment past the holiday if there is one, i.e. you can't leave it at zero, if you want it to skip the holiday. This one is straight-forward, relatively speaking!
The second formula - the same formula - looks slightly different from the first. The Due Date is a fixed # of days from the bill date but that is actual calendar days, not working days; however, the company wants the due date to be a workday. So:
- In thesection, I am saying "take the bill date + the number of days after for the due date" (in this case 18), and subtract 1 day.
- The Number of Days part is a 1. I want the due date to be 18 days after the bill date, 1 day after a holiday if there is one so to keep it at 18, I needed to subtract a day from the start date since I'm effectively adding a day to get past a holiday. Sounds confusing right? Try it yourself and you'll see if you leave that off and put 0 as the number of days, you don't get the same results.
I've highlighted a couple of examples where a straight (day + 18 day formula) vs. the WORKDAY formula yields different results, and is what we expect. At Christmas, the due date is a Wednesday which is correct, as the Monday and Tuesday are days the business is closed, in this case. Without that, it would naturally make the due date be the weekend of Christmas.
Here is the list of holidays, and it can be any dates where the business is closed or you have special shut-down schedules or anything you can think of.
It's a cool formula, and a time-saver if you thought there wasn't a way to do this in Excel.
