- Use =WORKDAY(start date, working days, holiday list) so due dates skip weekends and closed days.
- Keep three input cells: the start date, the working days and the holiday list.
- Lock the holiday range with dollar signs, and extend it when you add a holiday.
- Format the due date cell as a date, and retype any start date that does not behave like one.
- Check one job by hand before you rely on the sheet.
To set a service job due date in Excel that skips weekends and closed days, use the WORKDAY function: =WORKDAY(start date, working days, holiday list).
Key takeaways
Why adding calendar days breaks the schedule
Most dispatch sheets start with a simple idea: take the day a job came in and add the number of days the work should take. That works until the result lands on a Saturday, a Sunday, or a holiday when the shop is closed. Then the due date falls on a day nobody is at the bench, and the customer has been promised work that cannot start.
Take a job accepted late on a Friday. A plain count of calendar days from that Friday can land on a weekend. The dispatcher who set the date may not notice until it is already in the customer's hands, and by then the promise has been made.
The fix is to count working days instead of calendar days. Excel has a function built for this. Microsoft's documentation for WORKDAY states the rule plainly: working days exclude weekends and any dates identified as holidays. That one sentence is why the method works. You tell the sheet how many working days the job needs, and it skips the rest.
This post walks through a setup with three input cells. Once they are in place, every due date comes from one formula, and the dispatcher only has to enter a start date and a day count.
Set up the three input cells
Put the inputs on one sheet where everyone who books jobs can see them. A simple layout is enough. In A1, type the label Start date, and in B1 enter the date the job was accepted. In A2, type Working days, and in B2 enter the number of working days the job needs.
The third input is the holiday list. It is a list rather than a single value, so it gets its own column. Keeping the list in one straight column makes it easy to add a new date at the bottom without touching the rest of the sheet.
Label everything so a new dispatcher can read the sheet without a tour. The labels in column A tell the reader what each value means, and the Holidays heading tells them the list in column D is meant to be edited. If you add a notes line, keep it away from the formula's ranges so the layout stays easy to explain.
Format the start date as a date from the beginning. Keep the working days cell as a plain whole number. The holiday cells should also be real dates. A date stored as text looks right on screen but may not behave like one, and I come back to that problem later.
Write the due date formula
Put the label Due date in A3 and the formula in B3:
=WORKDAY(B1,B2,$D$2:$D$20)
Each part has a job. B1 is the start date, B2 is the number of working days, and $D$2:$D$20 is the holiday list. The dollar signs lock the holiday range so it keeps pointing at the same cells. Adjust the end row to match how many holidays you keep.
Give the holiday range more rows than you need today. If the range stops at the last row you filled, a holiday typed into the next empty row will not be counted. A longer range leaves room for additions. Whenever you add a holiday below the last row the formula covers, extend the range to include it.
The result is a serial number that represents a date. If B3 shows a plain number instead of a calendar day, the formula is fine and only the cell format is off. Format B3 as a date, and the due date will read like one. For the full wording of the three arguments, the starting date, the number of days, and the optional holiday list, read the WorkDay page.
Illustrative example: a Friday start
A job is accepted on a Friday and needs 5 working days. With no holiday in the range, the formula returns the Friday of the following week, because Saturday and Sunday are skipped. Now add a holiday on the Tuesday in between. The count skips that day as well, so the due date moves to the following Monday. No extra days are typed into the working days cell.
What each input changes
Each input does one job, and the reference table below gives the official sense of each one. Use it when you change a cell or wonder why a due date moved.
| Input | What the reference says |
|---|---|
| start_date | The starting date the count is measured from |
| days | The number of nonweekend and nonholiday days before or after start_date |
| holidays | An optional list of one or more dates to exclude from the working calendar, such as state and federal holidays and floating holidays |
| Sign of days | A positive value yields a future date; a negative value yields a past date |
The start date is the anchor. Change it and the whole calculation moves with it. If the start date in the cell is wrong, fix the cell rather than adjusting the day count to make up for it.
The days cell is the number of working days the job needs. You do not add extra days to cover a holiday in the middle of the job. The formula handles that, so the count should hold only the working days the job needs.
The sign matters too. A positive value gives a future date, which is what a due date needs. A negative value gives a past date. The documentation states this directly, and it makes the same formula useful for a quick look back. For example, you can find the day a job should have started in order to meet a promised completion.
Keep a short note of where each day count came from, such as a standard repair estimate, so a second dispatcher can see it without asking.
Pick one rule for the start date
The start date is only useful if everyone enters it the same way. Pick one rule, such as the day the job is accepted and logged, and write it at the top of the sheet where new dispatchers will see it. A written rule settles what a given row meant, which matters when a customer calls about a date weeks later.
Some shops have more than one possible start point. A job may be booked on a Monday, approved on a Wednesday, and started on a Friday. Choose the one that matches the promise you make. If the promise runs from approval, use the approval date. If it runs from booking, use the booking date. Whichever you pick, keep it the same for every job so the due dates can be compared fairly.
When the start date changes after a job is already on the sheet, change the cell and let the formula recalculate. Do not paste a new due date over the formula. A typed-in date looks correct until someone changes a holiday, and then it stops moving with the rest of the sheet.
Keep the holiday list current
The holiday list only works if it is complete. Its job is to exclude dates from the working calendar, including state and federal holidays and floating holidays. Floating holidays are easy to leave off, since they do not appear on every printed calendar. Check the list once a year, and again before any stretch of days when the shop will be closed.
Add shop closures to the same list. A closure for inventory, a training day, or a shutdown week is a day nobody works, so it belongs in the list just like a public holiday. Put a short note beside each date in column E if you want to remember why it is there. The formula reads only column D, so a note in E does not change the result.
Keep the list clean. Put only dates in it, with no labels, notes, or totals mixed in among them. A stray word in the middle of the list is easy to overlook as the list grows, and it is one more thing the formula has to read.
Watch for dates entered as text
Problems can occur when dates are entered as text, and this is one of the easiest causes of a due date that looks wrong to miss. The cell still shows something that resembles a date, so nothing seems broken.
If a start date or holiday does not behave like a date, retype it in the date format you use everywhere else and check the due date again. When you paste dates from an email, a customer portal, or another system, paste them as values and check one or two rows by eye before trusting the formula.
Check a due date by hand
A formula is easy to trust when you can confirm it on paper. Pick one job, write down the start date, and count forward one working day at a time. Skip Saturdays and Sundays, and skip every date in the holiday list. Stop when you have counted the number of days in B2. The date you stop on should match B3.
Do this check on a few jobs when you first set up the sheet, and again after you change the holiday list. Choose one job that crosses a holiday and one that starts on a Friday, since those are the cases where a plain count goes wrong most visibly. Once two or three checks match, you can rely on the formula for other jobs that use the same layout.
When a hand count and the sheet disagree, the table below points to the most likely cause. Work through the three inputs in the same order each time, starting with the start date, then the day count, then the holiday list. A fixed order keeps the search short.
| What you see | Likely cause | What to check |
|---|---|---|
| B3 shows a number, not a date | Cell format, not the formula | Format B3 as a date |
| Due date is one day off | Start date stored as text | Retype the start date and recheck |
| Due date lands on a closed day | Holiday missing from the list | Add the closure to column D |
| Due date ignores a new holiday | Range ends before the new row | Extend the holiday range |
Write the result of each check in a notes column or on paper. A record of which jobs were checked, and when, makes it easier to tell later whether a change to the holiday list was tested.
Give the customer a date with a margin
The formula returns the date the count lands on. How much slack to build in is a shop decision, and the sheet cannot make it for you. A margin is also a conversation with the customer. Telling a customer that the date includes room for a delayed part sets a more realistic expectation than a date with no slack that then slips.
Some dispatchers add a day of margin to the working-day count for jobs that depend on parts, and leave simple jobs at the exact count. Others quote the same count to every customer. Either way, write the rule down, so that a job with a margin and a job without one can be told apart later.
If you want a fixed margin, decide it once for the whole shop and write it in the sheet's notes. Avoid changing the day count by hand for each job. A count that has been quietly padded is hard to explain when a customer asks how the date was set.
Lock the formula and share the sheet
A dispatcher who types over B3 breaks the calculation for that job, and the error may not show until a customer asks why a date is wrong. Lock or shade the formula cell so it is clear it should not be typed over. Leave the start date, the working days, and the holiday list open, since those are the cells people should change.
If the sheet is shared with the bench or the office, keep one master copy and have people work from it rather than from copies. Copies drift. Two versions of a holiday list can disagree, and then two dispatchers quote different dates for the same job. For the handoff side of a shared sheet, see the Field Service Work Order Checklist for Dispatch Handoffs.
Store the holiday list in the same file, not in a separate note. When a holiday is added, the change goes in one place, and every due date recalculates from it.
Review the sheet at the end of each week
A short weekly check catches problems while they are still small. Look at every open job and ask whether its due date still makes sense. Jobs that were put on hold may need a new start date, and finished jobs can be cleared from the sheet. For how a booked visit is moved, see moving a booked visit.
Also scan the holiday list for the coming weeks. If a closure has been announced since the last check, add it now, not on the day it starts. A closure added late can leave jobs with due dates that fall on a day the shop is closed.
Start with one job
Pick the next job that comes in, enter its start date and day count, and compare the sheet's due date with a hand count. Once it matches, move the rest of the open jobs onto the sheet one at a time. Moving them all at once makes it hard to tell which row caused a problem.
Frequently asked questions
Which formula sets a service job due date in Excel?
Use =WORKDAY(start date, working days, holiday list), with the start date in B1, the working days in B2 and the holiday list in $D$2:$D$20.
Does the formula skip holidays as well as weekends?
Yes. Listed holidays are excluded from the count, so add each closed day to the holiday range.
Can the formula count back to an earlier date?
Yes. A negative value for the day count yields a past date, which helps when you need the day a job should have started to meet a promised completion.