Excel Date and Time Basics
Understand how Excel stores and handles dates and times to perform powerful calculations with confidence.
How Excel Stores Dates: The Serial Number System
The most important concept to understand is that Excel doesn't store a date like "December 1, 2025" as text. Instead, it stores dates as sequential serial numbers. This makes it possible to perform mathematical operations on them, like subtraction to find the number of days between two dates.
By default, Excel for Windows uses the 1900 date system, where time begins on January 1, 1900. This date has a serial value of 1.
| Date | Serial Value | Comment |
|---|---|---|
| January 1, 1900 | 1 | The beginning of Excel time. |
| January 2, 1900 | 2 | Two days after the start. |
| October 12, 2025 | 45941 | It is 45,941 days after January 1, 1900. |
How Excel Stores Time: Fractions of a Day
Just as dates are whole numbers, time is stored as a decimal fraction of a 24-hour day. The basic unit of time in Excel is one day.
- Half a day (12 hours or noon) is 0.5.
- An hour is 1/24, or approximately 0.04167.
- A minute is 1/(24*60), or approximately 0.000694.
When you see a date and time together, Excel is simply combining the whole number for the date and the decimal for the time.
Display vs. Value: How Excel Shows Dates and Times
A cell's format controls how the underlying serial number is displayed to you. Changing the format does not change the stored value. You can display the same serial value (e.g., 45941.75) in many different ways.
To change the format, right-click a cell, choose Format Cells, and go to the Number tab. Here you can pick from pre-set Date and Time formats or create a custom one.
Examples for Serial Value 45941.75
| Format Code | Result |
|---|---|
| m/d/yy | 10/12/25 |
| dd-mmm-yyyy | 12-Oct-2025 |
| mmmm d, yyyy | October 12, 2025 |
| h:mm AM/PM | 6:00 PM |
| m/d/yyyy h:mm | 10/12/2025 18:00 |
Entering and Interpreting Dates
Excel is smart and will recognize most standard date and time inputs, converting them to serial numbers automatically.
- If you enter
Feb-28without a year, Excel assumes the current year. - For 2-digit years, Excel interprets years 00-29 as 21st century (e.g.,
2/28/29becomes 2029) and years 30-99 as 20th century (e.g.,2/28/30becomes 1930). - Watch out for misinterpretation! If you type
3-4, Excel might see it as "March 4th". To enter it as text, type an apostrophe first:'3-4.
The 1904 Date System (for Mac)
Older Macintosh computers used a different date system that starts on January 2, 1904, as serial number 1. This can cause date discrepancies of 4 years and 1 day if a workbook is shared between a Mac using the 1904 system and a PC using the 1900 system.
While modern Excel versions handle this better, it's good to be aware of. You can check which system your workbook is using in Excel's options.
File > Options > Advanced. Scroll down to the "When calculating this workbook" section and check the "Use 1904 date system" checkbox. It should typically be unchecked for maximum compatibility.