Clean Excel dates and calculate a duration

작성자: AILesson6 분 소요테스트::ChatGPT검토일: 2026-08-28

빠른 답변

Normalize mixed date inputs before calculating calendar or working time. 제공할 내용: Observed date values, Columns and desired result, Duration rules. 예상 결과: A safe conversion and duration method with locale and boundary tests.

맥락 추가

텍스트는 이 브라우저에 유지됩니다. AILesson Prompts는 이를 모델이나 서버로 보내지 않습니다.

Observed date valuesColumns and desired resultDuration rules

프롬프트

채워지지 않은 필드는 플레이스홀더로 표시되므로 프롬프트를 복사하고 편집할 수 있습니다

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.

Playground에서 사용해 보기

기본적으로 비공개프롬프트 구성은 브라우저에서 로컬로 이루어집니다. 조직에서 허용하지 않는 한 기밀 정보를 AI 서비스에 입력하지 마세요.

입력에서 결과까지

적용 예시

구체적인 맥락이 이 레시피를 바로 사용할 수 있는 결과로 바꾸는 방법을 확인하세요

실제 입력

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.

예시 출력

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.

효과가 있는 이유

1
Normalization separates data conversion failures from duration logic.
2
Refusing ambiguous locale formats prevents plausible but incorrect dates.

결과 확인

Are ambiguous text dates flagged instead of guessed?
Is inclusivity defined for same-day and end-date cases?
Are negative durations visible as data errors?

안심하고 사용하세요

자주 묻는 질문

이 레시피를 언제 사용해야 하는지, 무엇을 제공해야 하는지, 그리고 어떤 부분에서 사람의 검토가 여전히 중요한지에 대한 실용적인 답변

What should I prepare before using “Clean Excel dates and calculate a duration”?

For “Clean Excel dates and calculate a duration,” prepare Observed date values, Columns and desired result, and Duration rules. Replace placeholders only with information you can verify. If a detail is unknown, preserve that uncertainty explicitly instead of asking the model to infer it.

When is the “Clean Excel dates and calculate a duration” result not ready to use?

The result is not ready if it does not yet deliver the stated outcome—A safe conversion and duration method with locale and boundary tests—from the supplied evidence, or if it relies on unresolved assumptions, missing approvals, or invented details. Use the checks as release gates: revise the source inputs or assign a named, authorized reviewer instead of polishing an unsupported output.

Which AI tools have recorded tests for “Clean Excel dates and calculate a duration”?

The published test record for “Clean Excel dates and calculate a duration” lists ChatGPT as of 2026-08-28. This confirms recorded runs, not guaranteed compatibility or identical results in later product versions. For another tool or version, keep every constraint visible and repeat the result checks before use.

더 많은 탐색 방법