Smart Statistics · Excel practical tutorial
Promise the Right Dispatch Date: An Excel Working-Day Tutorial
“Two days” can mean Thursday or Friday when the operation closes on Wednesday. Build a dispatch-date calculation that follows an agreed working calendar instead of simply adding days.
The dispatch promise desk
Illustrative example · fictional company calendarElapsed calendar days from the start
Illustrative example · all chart valuesCalendar and rule status
Working week: Monday to Friday
Excluded date: 4 November 2026, fictional company closure
Counting: start date is day zero
Inputs → eligible start → lead time → dispatch date
Editorial dashboard, not a live Excel screen. This is a dispatch plan, not a carrier delivery guarantee.
Agree the promise before writing the formula
A small UK supplier receives an order on Tuesday and promises dispatch after two working days. The team closes on Wednesday for stocktaking. Adding two to the receipt date gives Thursday; counting the actual working days gives Friday. A technically valid date can therefore create an operationally impossible promise.
This exercise uses a date-only rule: move any receipt on a non-working day to the next working day, then add the agreed number of working days. The eligible start is day zero. A zero-day lead means dispatch on that eligible start. There is no afternoon cut-off, hourly SLA, capacity check or carrier transit calculation.
Confirm that rule with the operations owner. If the business counts the receipt day as day one, or accepts orders only before a cut-off, this model must change before it is used with customers.
Build the three inputs
Working week
On a Settings sheet, format B2 as Text and enter
0000011. Select B2 and name it
WeekendPattern using the Name Box.
The seven positions run Monday to Sunday;
1 excludes a day and 0 allows it.
Closure calendar
Create a table named Closures with columns
Date and Reason.
For this exercise enter =DATE(2026,11,4)
and “Fictional stocktake closure”.
This is not a claimed bank holiday.
Order register
Create a table named Orders with columns
OrderID, ReceivedDate,
LeadDays, StartDate and
DueDate. Use Insert → Table and confirm
the headers.
Use Excel for Microsoft 365 for this exercise. Format date
columns as dd mmm yyyy and LeadDays as a whole
number. Keep the weekend pattern as text so leading zeroes
survive. The dates in Closures must be real Excel dates,
not text that merely looks like a date.
For production, replace the sample closure list with the operation’s approved calendar, including applicable regional holidays and company closures across the full planning horizon. Excel does not fetch them automatically. Keep one calendar per distinct operating schedule; do not mix unrelated sites’ closures into one list.
Calculate the eligible start date
Enter this complete formula in the first data cell of
Orders[StartDate]. Excel should fill the calculated
column. Blank receipt dates remain blank, non-numeric inputs are
flagged, and the date is moved to the first working day on or
after receipt.
=IF([@ReceivedDate]="","",IF(NOT(ISNUMBER([@ReceivedDate])),"Check date",IF([@ReceivedDate]<1,"Check date",WORKDAY.INTL(INT([@ReceivedDate])-1,1,WeekendPattern,Closures[Date]))))
The calculation starts one calendar day before receipt and
asks for one working day forward. This preserves a working
receipt date and advances a weekend or closure date.
INT deliberately removes the time portion;
it does not apply a cut-off rule.
Use Data Validation to restrict ReceivedDate to a sensible operational date range. A number can be a valid Excel serial date but still be the wrong business date. Reject implausible years and inspect imported data rather than assuming formatting proves validity.
Add the agreed lead time
Enter this complete formula in Orders[DueDate].
It allows zero or positive whole working days, flags invalid
lead times and uses the same calendar as StartDate.
=IF(OR([@StartDate]="",[@LeadDays]=""),"",IF(NOT(ISNUMBER([@StartDate])),"Check start",IF(NOT(ISNUMBER([@LeadDays])),"Check lead",IF(OR([@LeadDays]<0,[@LeadDays]<>INT([@LeadDays])),"Check lead",WORKDAY.INTL([@StartDate],[@LeadDays],WeekendPattern,Closures[Date])))))
Set LeadDays validation to Whole number, greater than or equal
to zero. The formula checks again because imported or pasted
values can require separate review. Do not wrap the whole
calculation in IFERROR(...,TODAY()):
a configuration problem must not turn into a plausible
dispatch promise.
Microsoft documents that WORKDAY.INTL excludes the supplied weekend and holiday dates and truncates fractional day offsets. This formula rejects fractional lead times instead of silently accepting that truncation. If your Excel locale uses semicolons as argument separators, use semicolons in place of the formula’s commas.
Prove the boundary cases
Enter these fictional test orders.
Use DATE(year,month,day) to avoid ambiguous date entry.
Keep the sample closure on 4 November 2026 and the
Monday-to-Friday pattern.
| Receipt | Lead | Start | Dispatch |
|---|---|---|---|
| 3 Nov 2026 | 2 | 3 Nov 2026 | 6 Nov 2026 |
| 6 Nov 2026 | 2 | 6 Nov 2026 | 10 Nov 2026 |
| 7 Nov 2026 | 0 | 9 Nov 2026 | 9 Nov 2026 |
| 4 Nov 2026 | 1 | 5 Nov 2026 | 6 Nov 2026 |
Now temporarily remove the fictional closure: the first order should move from 6 to 5 November. Restore it afterwards. Test a year-end order against next year’s approved calendar as well. A formula can be correct while an incomplete closure list makes the output wrong.
For a different weekly schedule, change the named setting and rerun the tests against manually agreed outcomes. Never assume the old expected dates still apply. When no extra closures apply, use a deliberately maintained empty calendar or a reviewed formula variant; do not enter a fake holiday to fill the table.
Turn a calculation into a controlled promise
Operations owns the rule
Approve the counting convention, weekly schedule and closure dates. Confirm whether the calculated date is feasible given stock and capacity before communicating it.
The workbook owner owns changes
Keep a calendar version and change log. Protect formula cells against accidental editing and restrict file access appropriately. Worksheet protection is an editing aid, not a substitute for access security.
Customer service owns communication
Label the output “Planned dispatch date”. Store the date actually promised separately with its issue date; later calendar changes must not silently rewrite the historical promise.
A deputy owns continuity
Assign a second person to update closures and run the test pack. Review the calendar before the planning horizon reaches an unmaintained period.
Release after a parallel check
Run the model beside the existing dispatch-planning process for a representative sample. Explain every date difference and include weekend, closure and zero-day cases. Ask a colleague who did not build it to enter an order and interpret a flagged input.
Release when the operations owner accepts the rule, the closure calendar covers the horizon, all agreed tests pass and invalid rows are held for review. If the work requires shifts, hourly cut-offs, multiple carrier calendars or capacity allocation, extend the design or move it into a more suitable scheduling system.
Measure manual date corrections, calendar-related promise changes and time spent checking orders. Compare planned dispatch with actual dispatch separately: a correct calendar calculation does not guarantee operational performance. Value any time saving using measured effort, rather than claiming that a formula creates cash savings.
Check your workbook before use
Common questions
Does Excel automatically know UK bank holidays?
No. Supply the non-working dates relevant to the operation in the holidays argument. Maintain the correct regional holidays and company closures for the planning horizon.
Does this calculate the customer’s delivery date?
No. It calculates a planned dispatch date under the stated rules. Carrier transit time, destination calendars and delivery commitments require separate logic.
What does a zero-day lead time mean here?
It means dispatch on the eligible start date. A weekend receipt is first moved to the next working day, so zero days does not promise dispatch on a closed day.
Can this handle hourly deadlines or afternoon cut-offs?
Not as written. This is a date-only model. Add an agreed cut-off and time-zone policy before using timestamps or promising time-of-day service levels.
Make the promise match the operation.
Smart Statistics can help turn spreadsheet rules into dependable planning tools, with clear inputs, tested calculations and reporting that explains exceptions.