Skip to content
Tutorials Center
My learning

Topics

Lesson 32 of 93 ยท Text, Date and Time

Dates and Times in Excel

Learn how Excel stores dates and times as serial numbers, build dates with DATE, read parts with YEAR, MONTH and DAY, and avoid the 1900 and 1904 date traps.

A date in Excel looks like 15-Mar-2025, yet the cell does not contain that text. It contains the number 45731. A time such as 6:00 PM is stored as the number 0.75. Once you know that, many mysteries disappear: you can subtract two dates to get the days between them, add 30 to get a due date, sort dates correctly, and also understand why a date can suddenly show up as a five-digit number.

In this lesson you will learn how serial numbers work, how to create and take apart dates and times with DATE, YEAR, MONTH, DAY, TIME, HOUR, MINUTE, SECOND and WEEKDAY, what TODAY and NOW really do, and about the famous 1900 leap-year quirk and the 1904 date system. Excel cannot run on this site, so JavaScript stand-ins reproduce the conversion rules so you can test them. Later lessons, such as Date Calculations, build on this one; for text versions of dates see TEXT, VALUE, NUMBERVALUE and Number-as-Text Problems.

A date is a number

Whole part Days since the start date.
Fraction The time of day, 0.5 is noon.
Format Only decides how the number looks.

45731.5 is March 15, 2025 at 12:00.

1 / 2

Serial numbers

In the default 1900 date system, serial number 1 is January 1, 1900, 2 is January 2, and so on. For a date from March 1, 1900 onward, the serial number is simply the count of days since December 30, 1899. A few anchors:

Date Serial number
1900-01-01 1
2000-01-01 36526
2025-03-15 45731
9999-12-31 (the last supported date) 2958465

The part after the decimal point is the time of day, as a fraction of 24 hours:

Time Fraction
06:00 0.25
12:00 0.5
18:00 0.75
1 hour 1/24 = 0.041666...
24 hours 1

So 45731.75 means March 15, 2025 at 6:00 PM. To see the number behind a date, change the cell format to General or Number. To make a number show as a date, use a date format (see Formatting Cells). The format is a costume; the value never changes.

Because dates are numbers, arithmetic just works:

TEXT
=B2 - A2            days between two dates
=A2 + 30            30 days after a date
=A2 + 7 * 4         four weeks later
=MAX(A2:A100)       the latest date

If a subtraction result shows up as a date such as 1/10/1900, Excel guessed a date format from the inputs. Switch the result cell to the General format to see the number of days.

Building and reading dates

  • DATE(year, month, day) builds a date from three numbers. It is the safest way to create a date in a formula, because it does not depend on regional settings the way the text "3/4/2025" does.
  • YEAR(date), MONTH(date) and DAY(date) read the parts back.
  • WEEKDAY(date, [return_type]) returns the day of the week; by default 1 is Sunday and 7 is Saturday. With type 2, Monday is 1 and Sunday is 7.
  • TODAY() returns today's date and NOW() returns the date and the current time. Neither takes arguments.

DATE is forgiving: values that are too large roll over into the next unit, and values that are too small roll back.

TEXT
=DATE(2025, 13, 1)     2026-01-01   (month 13 is January of the next year)
=DATE(2025, 3, 0)      2025-02-28   (day 0 is the last day of the previous month)
=DATE(2025, 1, 31+1)   2025-02-01
=DATE(25, 1, 1)        1925-01-01   (years 0 to 1899 are added to 1900!)

The "day 0" trick is how people find the last day of a month: =DATE(2025, 4, 0) gives March 31, 2025. Always give DATE a four-digit year.

For times, TIME(hour, minute, second) builds a fraction, and HOUR, MINUTE and SECOND read it back. Times never go beyond 24 hours in TIME: TIME(30, 0, 0) is the same as 6:00 AM. To display more than 24 hours in a cell, use the format [h]:mm, which shows 30:00 for 1.25 days.

TODAY and NOW are volatile: they are recalculated whenever Excel calculates, so a workbook opened tomorrow shows tomorrow's date. For a date that must not change, enter it as a fixed value with Ctrl+; (current date) or Ctrl+Shift+; (current time).

The 1900 leap-year quirk

Excel's serial system contains a deliberate bug. It treats the year 1900 as a leap year, so it has a February 29, 1900 (serial 60) that never existed. The mistake was inherited from the spreadsheet Lotus 1-2-3 for compatibility and was kept so old files still open correctly. Three consequences follow:

  • From March 1, 1900 onward, serial numbers and the real calendar agree. All dates you will normally use are safe.
  • For serials 1 to 59 (January 1 to February 28, 1900), Excel's weekday is wrong: it calls January 1, 1900 a Sunday, while it was really a Monday.
  • Serial 0 is displayed as 1/0/1900, and negative serials are not valid dates in this system. Excel cannot represent dates before 1900.

The practical rule: do not use Excel for historical calendar math before March 1900, and do not rely on WEEKDAY for those dates.

The 1904 date system

Excel can count from a different start. In the 1904 date system serial 0 is January 1, 1904. It was the default on older Mac versions of Excel (it avoids the fake leap day issue by starting later). You can find it under File โ†’ Options โ†’ Advanced โ†’ When calculating this workbook โ†’ Use 1904 date system. The setting belongs to each workbook.

The two systems differ by exactly 1462 days (four years and one day). If a date moves from one workbook into the other by copy and paste and the two use different systems, it appears four years and one day wrong. When you see a date like 2029 instead of 2025, suspect this. Check Use 1904 date system in both workbooks.

Run it: serial numbers in JavaScript

The functions below follow the rules above: the 1900 system with its fake leap day, DATE rollover, WEEKDAY, times as fractions and the 1904 shift. They are a stand-in for Excel's behavior (they cover normal cases, not every edge). The "current time" is a fixed value so the output is always the same.

JS Serial numbers, DATE and the fake leap day

Press Run (or Ctrl + Enter) to see the console output here.

No output.

Times as fractions

Because times are fractions of a day, you add and subtract them like any number, and you convert to hours by multiplying by 24.

JS Time of day, durations and floating-point care

Press Run (or Ctrl + Enter) to see the console output here.

No output.

The last line explains why equality tests on times can fail: fractions are binary floating-point numbers and tiny errors appear. Compare with ROUND, or work in whole seconds or minutes.

The same ideas in Python

In pandas, pd.to_datetime(serial, unit="D", origin="1899-12-30") converts Excel serials to real dates (for modern dates), which is useful when you read a sheet that stored dates as numbers.

Python Excel serial numbers in pandas

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

The last line prints 1462, the exact difference between the two date systems.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. What does the number 45731.5 represent in a date-formatted cell?

    The whole part (45731) is the date, March 15, 2025. The fraction 0.5 is half a day, so 12:00 noon.
  2. 2. What does =DATE(2025, 3, 0) return?

    Day 0 means the day before the first of the month, so this is the last day of February 2025, which is the 28th.
  3. 3. Why does Excel have a February 29, 1900?

    The error was copied from Lotus 1-2-3 and kept so that old files stay valid. Dates from March 1, 1900 onward are not affected.
  4. 4. A date copied from another workbook is exactly 1462 days off. What is the likely cause?

    The 1900 and 1904 systems differ by 1462 days. Check the "Use 1904 date system" option in both workbooks.
  5. 5. You need a date that never changes after you enter it. What should you use?

    TODAY and NOW are volatile and update whenever Excel calculates. Ctrl+; inserts the current date as a fixed value.

Summary

  • Excel stores a date as a serial number (days) and a time as a fraction of a day; the format only changes the display.
  • DATE, YEAR, MONTH, DAY, WEEKDAY, TIME, HOUR, MINUTE and SECOND build and read dates and times; DATE rolls over out-of-range values, which gives tricks such as "day 0".
  • TODAY and NOW are volatile; use Ctrl+; for a fixed date.
  • Serial 60 is a fake February 29, 1900, so use real calendar math only from March 1, 1900.
  • The 1904 date system shifts every date by 1462 days; check the setting when dates look four years off.

Next: Date Calculations: DATEDIF, EDATE, EOMONTH, NETWORKDAYS.