How Excel Stores Dates and Times

Excel stores every date as a whole number (the number of days since 1 January 1900) and every time as a fraction of a day. Once you know this, date maths is simple: subtracting two dates gives the days between them, and adding 7 moves a date a week ahead.

11-Jan-1900
4630409-Oct-2026
0.512:00 noon
1 hour1/24 of a day

Dates are numbers

Serial number 1 is 1 January 1900, 2 is 2 January 1900, and so on. Today, 9 October 2026, is 46304. The cell's number format decides whether you see 46304 or 09-Oct-2026.

Serial numbers and the dates they represent in Excel

To see the number behind a date, select the cell and press Ctrl + Shift + ~ (General format). Press Ctrl + Z to undo.

Times are fractions of a day

Time Fraction of a day Value in Excel
24 hours 1 1
1 hour 1/24 0.041667
1 minute 1/1440 (24 × 60) 0.000694
1 second 1/86400 (24 × 60 × 60) 0.0000116
06:00 6/24 0.25
18:00 18/24 0.75
23:45:36 0.99 × 24 hours 0.99
Decimal values and the times they represent in Excel

Date and time together

The whole number is the date and the decimal part is the time. 1.5 is 1-Jan-1900 at 12:00 noon, and 46304.75 is 09-Oct-2026 at 6:00 pm.

1.5 shown as 01-01-1900 12:00:00
To get Formula
Just the date =INT(A2)
Just the time =MOD(A2,1)
Date and time from two cells =A2+B2

Date maths that works because dates are numbers

Task Formula Result
Days between two dates =B2-A2 A number of days
A date 30 days later =A2+30 A date
Days left until a deadline =B2-TODAY() Updates every day
Hours between two times =(B2-A2)*24 Hours as a number, e.g. 8.5
Add 90 minutes to a time =A2+90/1440 A time

For working days, months and years, use NETWORKDAYS, EDATE and DATEDIF. For time sheets, see time difference and hours worked.

Shortcuts

Shortcut Enters
Ctrl + ; Today's date (doesn't change later)
Ctrl + Shift + ; The current time
=TODAY() / =NOW() Today's date / date and time, updating every time the sheet recalculates

When dates don't behave

Problem Cause Fix
A date shows as 46304 The cell is formatted as General or Number Ctrl + Shift + # applies a date format
Dates sit on the left and won't sort They're text, not real dates (often from a CSV or another system) Data → Text to Columns → Finish, or =DATEVALUE(A2)
=ISNUMBER(A2) returns FALSE Same: a date stored as text As above
Day and month swapped (03/04 read as 4 March) The file and your PC use different date orders Text to Columns → step 3 → Date: DMY
Total hours show 02:00 instead of 26:00 The time format wraps at 24 hours Custom format [h]:mm
Negative times show #### Excel can't show negative times Calculate in hours instead: =(B2-A2)*24

Excel treats 1900 as a leap year (29-Feb-1900 exists in Excel, though not in real life). It only affects dates before March 1900. Mac files may use the 1904 date system, which shifts every date by 1,462 days: check File → Options → Advanced → Use 1904 date system.

Related

Last updated · Written by Ajay Anand, Microsoft Excel MVP