A plausible rollover hides a bad record
DATE can move February 30 into March and TIME can wrap hours or minutes. A round-trip check prevents this from passing as a valid import.
Convert fixed-width local date and time text
Read-only
Step-by-step guide · read-only ·
Loading your interactive spreadsheet…
Preparing the worksheet and formulas…
Data & tools
Task requirements
Fill B2:B5 with a numeric Excel timestamp for exact YYYY-MM-DD HH:MM text from years 2000 through 2099. Preserve blank inputs as blank text. Return Review for every malformed, invalid or out-of-range input. Interpret the text as a local clock reading with no timezone conversion.
Follow the highlighted cells. Switch to Your turn to edit. Answer cells: B2, B3, B4, B5. Browser calculations may differ from your Excel version.
Selection & formula search
Practice stays on this device. No worksheet data is uploaded.
Notes & examples
About this lesson
Build a real numeric timestamp from explicit components, then reject values that normalize to a different date or clock time.
THE QUICK ANSWER01
Build a real numeric timestamp from explicit components, then reject values that normalize to a different date or clock time.
ƒx
=IF(A2="","",IFERROR(IF(AND(LEN(A2)=16,--LEFT(A2,4)>=2000,--LEFT(A2,4)<=2099,TEXT(DATE(--LEFT(A2,4),--MID(A2,6,2),--MID(A2,9,2))+TIME(--MID(A2,12,2),--RIGHT(A2,2),0),"yyyy-mm-dd hh:mm")=A2),DATE(--LEFT(A2,4),--MID(A2,6,2),--MID(A2,9,2))+TIME(--MID(A2,12,2),--RIGHT(A2,2),0),"Review"),"Review"))For the example data
B2 2026-10-18 13:45B3 2024-02-29 00:01B4 2025-12-31 23:59B5 2000-01-01 00:00
The formula uses the interactive worksheet above. Adjust ranges and criteria for your own data.
How it works
A timestamp is a number
A whole day and a day fraction share one numeric value. The yyyy-mm-dd hh:mm format changes the display, not the answer type. TEXT is used only to validate the round trip; the accepted result remains numeric.
Parsing a local clock is not timezone conversion
These invented source strings carry no offset or timezone. The lesson neither infers a timezone nor resolves daylight-saving transitions. Use an explicit timezone-aware workflow before measuring elapsed time across such changes.
Read the full step-by-step walkthrough
- Declare the input grammar
The import uses 16 characters, hyphens in the date and a colon in the clock time. A blank is deliberately different from a malformed record.
- Extract year, month and day
Read fixed positions and pass numeric components to DATE. DATE can normalize out-of-range components; successful conversion alone does not prove that the original date was valid.
- Add the clock fraction
TIME returns a day fraction. Add it to the whole date to retain hours and minutes in one numeric value.
- Compare the normalized result with the source
Format the constructed timestamp back to the declared grammar. A rollover, missing zero, changed separator or extra character fails the exact round trip.
- Bound the year and review parsing errors
The task accepts 2000 through 2099. IFERROR keeps malformed text reviewable; it does not substitute zero or an invented date.
Things to watch
A formatted text result is not a timestamp
Returning TEXT for valid rows makes a string. The required answer is numeric so later date and duration formulas can use it.
About this example
Formula entry, fill and all six authored datasets were checked in Chrome on October 18, 2026. Numeric timestamp/clock display and original/current/frozen downloads have separate package/browser checks. Local clocks carry no timezone or daylight-saving interpretation; no payroll policy is inferred. Native Excel scope is recorded in the date/time validation report; Google Sheets remains unverified.
Content version 1. Practice checks compare calculated results across different datasets; they do not certify every possible formula.