Organize information

Clean Excel dates and calculate a duration

Normalize mixed date inputs before calculating calendar or working time

6 min setupTested with:ChatGPTReviewed: 2026-08-28

Add your context

Your text stays in this browser. AILesson Prompts does not send it to a model or server.

Observed date valuesColumns and desired resultDuration rules

Your prompt

Unfilled fields remain visible as placeholders, so you can still copy and edit the prompt

Design a safe Excel method to normalize dates and calculate duration.

Observed inputs:
[inputs]

Workbook layout and desired result:
[layout]

Duration rules:
[rules]

First distinguish real Excel serial dates from text and identify ambiguous locale-dependent values. Do not guess whether 03/04 means March 4 or April 3. Propose helper columns for normalization and validation before duration calculation. Return formulas, cell formats, invalid-value flags, and tests for blanks, timestamps, reversed dates, leap days, and boundary inclusion. State whether the result is elapsed time, calendar days, or working days.

Private by defaultPrompt assembly happens locally in your browser. Avoid placing confidential information into any AI service unless your organization allows it.

From input to outcome

A worked example

See how concrete context turns this recipe into a usable result

Actual input

Observed date values: B contains real Excel dates or ISO text such as 2026-02-27; C contains real dates, ISO text, or blank for open cases. Values like 03/04/2026 may occur and must be flagged as ambiguous. Some cells include timestamps.
Columns and desired result: B: Started; C: Completed; D: Clean Start; E: Clean Complete; F: Calendar Days. Data starts at row 2.
Duration rules: Use 1 March 2026 for open cases in this fixed report. Count elapsed calendar days, so same-day completion is 0. Preserve timestamps only long enough to remove them. Reversed dates must show Review.

Example output

Use helper columns so conversion problems remain visible.

D2 clean start: =IF(B2="","",IF(ISNUMBER(B2),INT(B2),IF(AND(LEN(B2)=10,MID(B2,5,1)="-",MID(B2,8,1)="-"),DATE(LEFT(B2,4),MID(B2,6,2),RIGHT(B2,2)),"Review")))

E2 clean complete: use the same conversion for C2, but return DATE(2026,3,1) when C2 is blank.

F2 elapsed calendar days: =IF(OR(D2="Review",E2="Review",D2=""),"Review",IF(E2<D2,"Review",E2-D2)). Format D:E as an unambiguous date such as yyyy-mm-dd and F as a number.

The formula accepts serial dates and exactly shaped ISO text. It deliberately flags 03/04/2026. INT removes time from serial values. Tests: same day → 0; 27 Feb to 1 Mar 2026 → 2; blank completion uses 1 Mar; 29 Feb 2024 parses correctly; completion before start → Review. Validate imported ISO strings with impossible months or days because DATE can roll invalid components forward.