The workflow at a glance
Under this calendar, W01 and the weekend-start ticket W02 each took 1 workday. W03 closed on its opening date and took 0; W04 took 2. W05 has no closing date: report its open age of 2 workdays as of October 29, 2026, without inventing a zero-day completion or adding it to the closed-ticket performance denominator.
Write the reporting rule before selecting a formula
Business-day duration is neither simple date subtraction nor every available count minus one. Fix the calendar first: Monday through Friday are workdays, Saturday and Sunday are not, and October 26, 2026 is an additional company holiday in this exercise. That holiday is an editorial invention, not a statement about public holidays in any jurisdiction.
This case compares dates without hours or minutes. Exclude the opening date; count qualifying calendar dates after it through the closing date. A same-day closure is zero. The teaching target is a closed duration of no more than 1 workday. A team that counts the opening date, starts at the next shift or measures working hours needs a different contract and different reference answers.
Microsoft’s NETWORKDAYS.INTL reference documents configurable weekends and a holiday range. The mask 0000011 marks Saturday and Sunday as nonworking. Those are function capabilities; they do not establish your organization’s endpoint policy.
Keep three decisions separate
Date meaning
opened_date and closed_date use the same company calendar. They are not unresolved timestamps awaiting a time-zone conversion.
Work calendar
Weekend mask 0000011; the sole additional holiday is October 26, 2026.
Reporting contract
Exclude the start date and count eligible end dates. Closed durations ≤1 day meet the target; open ages stay separate.
Give AI all five source records and the calendar contract
The inputs, prompt, faulty draft and reference answers below are original KitNelo teaching materials. They contain no real customer records and are not claimed output from a tested model. The dates deliberately cover a Friday start, a weekend start, same-day closure and an open record.
Keep ticket_id throughout. Do not let AI complete W05’s missing closing date to produce a tidy table. If the source contains an ambiguous expression such as 03/11/2026, use the date meaning and normalization workflow first. A parsable value does not establish its business meaning.
Task: return a row-level review of the complete CSV below without changing the source dates.
Calendar: Monday–Friday are workdays, Saturday/Sunday are not, and 2026-10-26 is an additional holiday in this teaching example.
Count: exclude the opening date; include qualifying workdays after it through the closing date. Include the closing date only if it is a workday. Same-day closure is zero.
Closed records with ≤1 workday are within_target; >1 are over_target.
Preserve blank closing dates. Set closed_result to open and calculate open age as of 2026-10-29 in a separate field. Do not treat that record as closed.
Output ticket_id, closed_workdays, closed_result and open_age_workdays. List the dates counted for each record. Preserve all five records; do not infer holidays or fill missing dates. A closing date before its opening date requires REVIEW, not a normal duration.
Explain the interval and calendar before the answers. Do not use a dynamic current date.
SOURCE CSV:
ticket_id,opened_date,closed_date
W01,2026-10-23,2026-10-27
W02,2026-10-24,2026-10-27
W03,2026-10-27,2026-10-27
W04,2026-10-27,2026-10-29
W05,2026-10-27,List the counted dates before comparing totals
W01 opens on Friday, October 23. Exclude the 23rd under the start-date rule; the 24th and 25th are the weekend, and the 26th is the exercise holiday. Only the 27th counts. W02 opens on Saturday the 24th, a date that contributes no workday to begin with. It also counts only the 27th, so the two records must agree.
W03 opens and closes on the 27th. There is no date after the opening date in its interval, so its duration is zero. W04 counts the 28th and 29th, giving 2. W05 has no completion event. You can measure its age through a fixed cutoff, but its state remains open.
| Record | Dates counted | Excluded or unresolved | Reference |
|---|---|---|---|
| W01 | Oct 27 | Oct 23 start; 24–25 weekend; 26 holiday | Closed: 1 day; within target |
| W02 | Oct 27 | Oct 24 start/weekend; 25 weekend; 26 holiday | Closed: 1 day; within target |
| W03 | None | Oct 27 is both opening and closing date | Closed: 0 days; within target |
| W04 | Oct 28 and 29 | Oct 27 opening date | Closed: 2 days; over target |
| W05 | Oct 28 and 29 through the Oct 29 cutoff | Closing date not supplied | Open: age 2 days |
Deliver four closed results and one open age
Blank fields here mean not applicable, rather than an omitted zero. W05’s closed_workdays stays blank. The closed records have no open_age_workdays value. Retain the original dates in the source sheet and match the delivery table through ticket_id.
There are 4 closed records, of which 3 meet the exercise target: 3 ÷ 4 = 75%. W05’s age does not turn it into a fifth closed failure and change the metric to 3 ÷ 5 = 60%. A measure of all outstanding and completed work would need its own stated definition.
ticket_id,closed_workdays,closed_result,open_age_workdays
W01,1,within_target,
W02,1,within_target,
W03,0,within_target,
W04,2,over_target,
W05,,open,2Subtract the opening date’s actual contribution, not a blanket one
Put the source CSV in A1:C6: A is ticket_id, B is the opening date and C the closing date. Set H2 to the real date value October 26, 2026. Nonblank B and C entries must also be real date values; changing the display format of text is insufficient. Enter this candidate in D2 and fill through D6. The exercise has one holiday; a larger real holiday list requires an extended fixed range.
The expression first preserves blank closing dates and flags closures before opening. For other records, it counts the interval including the opening date, then subtracts that date’s contribution under the same calendar: one for a working opening date, zero for a nonworking opening date. Same-day closure therefore becomes zero without incorrectly deducting a weekend day.
For Google Sheets, the official function help likewise requires meaningful date arguments and warns that typing 10/10/2000 directly in a formula is interpreted as successive division. Use date cells here. Regional settings may require another argument separator. Resolve types and syntax before comparing the business answers.
=IF(C2="","",IF(C2<B2,"REVIEW",NETWORKDAYS.INTL(B2,C2,"0000011",$H$2:$H$2)-NETWORKDAYS.INTL(B2,B2,"0000011",$H$2:$H$2)))W02 exposes an inclusive count minus one
=NETWORKDAYS.INTL(B3,C3,"0000011",$H$2:$H$2)-1 W02: only Oct 27 is a workday in the interval, so 1-1=0.
1 - NETWORKDAYS.INTL(B3,B3,"0000011",$H$2:$H$2) W02: Oct 24 is Saturday and contributes zero, so 1-0=1.
Repair the duration and the summary denominator
An apparently complete faulty draft might say: “W02 finished the same day, W05 took zero days, and four of five tickets met the target: 80%.” That combines three defects: a weekend start is over-subtracted, a missing closure becomes a completion, and the denominator mixes open and closed records. Correcting a formula alone does not repair the final sentence.
Give AI the exact differences: W02 is 1 day; W05 has 2 only in open_age_workdays; the closed set must be precisely W01–W04. Request the counted dates and the 3 ÷ 4 calculation again. Check that the revision has not converted “1 workday” into “24 hours” or presented the invented holiday as a public holiday.
Keep the calendar version, endpoint contract, cutoff, formula version and unresolved records with a real report. If source data contains timestamps, establish time zones, working hours and pause rules before adopting date-level counting. This exercise does not implement an hourly service-level agreement.
Run checks that could actually disprove the answer
Retain expected and actual values for each boundary. The plan below is proposed acceptance work, not a claim that this page has executed Excel, Sheets or your AI account. Run it in a copy and send failures to the report owner.
Use the row-level and change-based formula checks: change one input, then restore the original before the next check. A numeric display does not establish the contract, and asking the same model again does not replace the independent calendar ledger.
Acceptance conditions for this exercise
- All five ticket IDs remain: four closed and one open. Missing dates were not filled with today.
- Closed durations D2:D5 are 1, 1, 0 and 2; W05’s closed duration remains blank.
- W05’s age uses the explicit 2026-10-29 cutoff; its value of 2 is not presented as a closed outcome.
- The target calculation is 3/4=75%, with the teaching threshold of ≤1 workday stated.
- Temporarily put W04’s closing date before its opening date: expect REVIEW, then restore the source.
- Remove the extra holiday from the calendar: W01 and W02 should each gain Oct 26. No change suggests a holiday-reference or date-type defect.
Common questions
Why not count the open ticket’s 2 days as a closed failure?
You can define an open-work warning separately, but there is no closing event yet. Do not relabel open age as final duration or silently change the denominator of a closed-ticket metric.
Should I move a weekend opening date to Monday?
Keep the actual opening date. A team may choose to start counting on its next workday, but must also say whether that day counts. In this exercise Monday, October 26 is itself a holiday; changing the date cannot replace a clear rule.
