Date and time · date formatsExcel serial number to date and back
Excel stores a date as a day number: 45000 is 15 March 2023, and the fraction is the time of day. Paste a number from a cell to get the date and time; enter a date to get the number. Both date systems, 1900 and 1904, and the 29 February 1900 that never existed.
Calculated in your browser — values are never sent anywhere
A date in Excel is an ordinary number in a cell formatted as a date. The whole part is the day count from the epoch, the fraction is the share of the day: 0.5 is noon, 0.75 is 6 pm. That is why dates can be subtracted (you get days) and added to numbers.
By default the count starts on 1 January 1900, which is day 1. Excel treats 1900 as a leap year — Lotus 1-2-3 did, and compatibility was kept. Serial 60 is 29 February 1900, a day that never happened, and every date before 1 March 1900 is off by one.
The second system is 1904, where 0 is 1 January 1904. Old Excel for Mac used it, and it lives on in workbooks with “Use 1904 date system” ticked. The same date is 1462 lower there — hence dates that jump four years when copied between workbooks.
Part by part45000
45000
whole part — the day number: 15 March 2023
.75
fraction — share of the day: 0.75 × 24 = 18 hours
1900
date system: day 1 is 1 January 1900
How to do it yourself
=DATE(2023,3,15)a date from year, month and day — the cell holds 45000
=TEXT(A1,"dd.mm.yyyy")the number as a text date for export
=INT(A1)the day number without the time — a date cell is that number
=MOD(A1,1)*24the time from the fraction, in hours
03
What it looks like
values you meet in practice
Value
What it is
1
1900-01-01 00:00:00
60
1900-02-29 00:00:00 — does not exist
61
1900-03-01 00:00:00
25569
1970-01-01 00:00:00
45000
2023-03-15 00:00:00
45000.75
2023-03-15 18:00:00
2958465
9999-12-31 00:00:00
04
Common mistakes
A date turned into a number like 45000 — the cell format is General or Number. Nothing is broken: set a Date format and the number becomes a date again.
Dates moved by exactly 4 years and 1 day — the workbooks use different date systems (1900 and 1904). The gap is 1462 days; the switch is in Options → Advanced → Use 1904 date system.
The fraction is not minutes: 45000.30 is not 00:30 but 07:12, because 0.3 of a day is 7.2 hours. Multiply the fraction by 24 instead of reading it as hours and minutes.
05
Frequently asked
Because an Excel date is a number: the count of days since 1 January 1900, with the time as the fraction. You see the number when the cell is formatted as General or Number — common after pasting from another program or exporting to CSV. Nothing is broken: apply a Date format and 45000 becomes 15 March 2023 again.
Select the cells and choose a Date format on the Home tab or in Format Cells. If you need the date as text — for export or to join with other text — use TEXT(A1,"dd.mm.yyyy"). To check a single number without Excel, paste it into the field on this page and get the date, the time and the weekday.
Give the date cell a General format and its serial number appears instead of the date. With formulas, INT(A1) returns the day number without the time, and a plain =A1 in a General cell shows the number with its fraction. If the date is stored as text, turn it into a real date first with DATEVALUE(A1) or Data → Text to Columns.
A second way of counting: day 0 is 1 January 1904 rather than 1900. Old Excel for Mac used it, and it survives as a workbook setting — Options → Advanced → Use 1904 date system. The same date is 1462 lower there, and the 29 February 1900 bug does not exist. Negative times display in it, while the 1900 system refuses them.
The data was copied between workbooks with different date systems: one counts from 1900, the other from 1904. The number in the cell stayed the same, but the date it means moved by 1462 days — exactly four years and one day. Fix it by adding or subtracting 1462 with Paste Special, or by aligning the date system setting in both workbooks.
The time is the fraction of the number, a share of the day. Multiply it by 24 to get hours: MOD(A1,1)*24. For 45000.75 that is 18 hours. To see the time in the familiar form, format the cell as Time or use TEXT(A1,"hh:mm:ss"). A common slip is reading the fraction as minutes: 0.30 is not half an hour but 7 hours 12 minutes.