Excel Date Serial Numbers and Time Fractions Explained

Confirm the source and whether the value is numeric

The steps below use Windows desktop Excel for Microsoft 365, 2024 or 2021, with English formula names and comma argument separators. The synthetic serial column uses the 1900 system. If your Excel uses semicolons between arguments, adapt the formula separators. Import the serial field as a decimal number, not a whole number.

A source specification or a known calendar record must establish that these are dates. An order ID can also produce a plausible date when formatted. CSV does not carry a workbook date-system setting. Keep the raw file and that setting together.

Put the sample in A1:B5 and enter =ISNUMBER(B2) in C2; fill through C5. Each sample serial should return TRUE. FALSE means a text value, blank or another nonnumeric input needs investigation; it does not prove that the date format is wrong. Do not turn an absent value into zero.

For a confirmed text serial with a decimal point, a separate helper such as =IF(B2="","",NUMBERVALUE(B2,".",",")) preserves blanks and interprets those declared separators. Use it only for the known numeric field; it is not a validator for arbitrary timestamps, IDs or percentage strings.

Related help: Diagnose calendar dates that are stored as text

Why 0.5 and 0.75 represent fractions of a day

A complete day is 1. There are 24 hours, 1,440 minutes and 86,400 seconds in that unit. Divide an hour count by 24, minutes by 1440, or seconds by 86400 to express it in days. Thus 18/24, 1080/1440 and 64800/86400 all equal 0.75.

The number 0.5 is half a day, not half an hour. To express half an hour as a day fraction, use =30/1440 or =TIME(0,30,0), then display the result as hh:mm:ss. Both should show 00:30:00. Keep the division formula instead of typing a rounded decimal such as 0.02.

These examples are times of day. Display the fractions alone as hh:mm:ss, without attaching a calendar date. A value of 1 is a whole day; an ordinary clock format wraps to 00:00:00. That display does not mean the stored value is zero.

Display a date plus its time without losing either

In D2 enter =B2 and fill through D5. Select D2:D5, press Ctrl+1, choose Custom, and enter yyyy-mm-dd hh:mm:ss. Widen the column if it displays #####. The result should match the expected sample. This is number formatting; the helper still contains the original numeric value.

Use the formulas below to inspect the first row, then fill down. Format E as General, F as General, G as hh:mm:ss and H as General. For record_id D4 (worksheet row 5, input B5), the date part is 46024, the fraction is 0.75 and the time is 18:00:00. Multiplying only that fraction by 24 gives 18 decimal hours; multiplying the entire serial also includes all its date days.

A format of yyyy-mm-dd hides the time without removing it. A format of hh:mm:ss hides the calendar date. In the combined format, the mm after hh means minutes; the earlier mm means month. If you need a text label, TEXT can produce one, but retain the numeric column for date arithmetic.

D2: =B2                 Format: yyyy-mm-dd hh:mm:ss
E2: =INT(B2)            Format: General (whole date part)
F2: =MOD(B2,1)          Format: General (fraction of a day)
G2: =MOD(B2,1)          Format: hh:mm:ss (time only)
H2: =MOD(B2,1)*24       Format: General (decimal hours)

Fill D2:H2 down through row 5.
Independent control: =DATE(2026,1,2)+TIME(18,0,0)
In a 1900-system workbook, the control is 46024.75.

Check the 1900 or 1904 system before correcting a shift

For 1 January 2026, the 1900 serial is 46023 and the 1904 serial is 44561, separated by 1462 days. In Windows, inspect File > Options > Advanced > When calculating this workbook > Use 1904 date system. Confirm the source setting separately.

For unconverted modern serials, add 1462 when moving from 1904 to 1900, and subtract it for the reverse direction. Fractions stay intact: 44562.75 + 1462 = 46024.75. Only apply this after a known record confirms the mismatch; copying between workbooks may already convert values. Do not change the entire workbook setting to repair one imported column.

Distinguish hidden time from time already discarded

If MOD(B2,1) is nonzero, a date-only display can reveal the time again by adding hh:mm:ss to the format. If the stored value was replaced by INT(B2), rounded to an integer, or exported with only the date text, formatting cannot reconstruct its former time. Retrieve the original workbook or a source export that still has the fraction.

For the sample value 46024.75, INT returns 46024, while ROUND(value,0) returns 46025 and moves the displayed date to the next day. These are value changes, unlike choosing a shorter display format. Keep a raw column when intentionally producing a date-only result.

A zero fractional part is consistent with midnight, but it does not prove that the source recorded midnight: an earlier process might have removed the time. Preserve that uncertainty. Spreadsheet serials also carry no time-zone identity; local time and UTC require separate source metadata.

Flag the early-1900 exception rather than guessing

The 1900 system retains a compatibility error that treats 1900 as a leap year. Serial 60 denotes the fictitious 29 February 1900, which is not a valid Gregorian date. Reject or label that case explicitly when exporting to another date system.

These inspection formulas and modern examples do not constitute a universal converter for historical dates or negative serials. January and February 1900 need explicit handling of Excel's convention; do not extend a convenient modern base-date shortcut to them. The CSV checker does not inspect workbook settings or convert serials.

The synthetic numeric, display and type controls were executed in Excel 16.0, build 5569.0, on September 13, 2026, in separate 1900- and 1904-system workbooks. DATE, TIME, INT, MOD and TEXT produced the stated examples, including the serial-60 compatibility display. This checks the native calculation and formatting engine, not every import dialog or locale.