The Complete Guide to Dates in Excel

How Excel Actually Stores Dates

Here's the fundamental thing to understand: Excel doesn't store dates as dates. It stores them as numbers.

January 1, 1900 is day 1. January 2, 1900 is day 2. And so on. Today's date — whatever day you're reading this — is probably somewhere around day 45,000-something.

When you see “January 15, 2025” in a cell, Excel is actually storing the number 45,672. The visual display is just formatting applied to that number.

This system is called the “serial date” system, and it's actually clever. It makes date arithmetic trivial. Want to know how many days between two dates? Just subtract them. Want to add 30 days? Just add 30. The math just works.

Times work similarly. They're stored as decimal fractions of a day. 0.5 is noon. 0.75 is 6:00 PM. So “January 15, 2025 at 6:00 PM” is stored as 45,672.75.

The 1900 Leap Year Bug

Here's a fun fact: Excel thinks 1900 was a leap year. It wasn't.

This bug has existed since Excel 1.0, and it's intentional. When Microsoft created Excel, they wanted compatibility with Lotus 1-2-3, which had the same bug. So Microsoft copied it.

In practice, this rarely matters. The bug only affects dates in January and February 1900, which most people never use. But it does mean Excel's date serial numbers are off by one compared to what they “should” be, and if you're ever doing date calculations that span from before March 1, 1900 to after, you might see weirdness.

Formatting vs. Reality

The source of most date confusion in Excel is the difference between how a date is stored and how it's displayed.

The number 45,672 can be displayed as:

• “1/15/2025” (US short date)
• “15/01/2025” (European short date)
• “January 15, 2025” (long date)
• “2025-01-15” (ISO format)
• “Wed, Jan 15” (custom format)
• “45672” (as a number)

All of these are the same underlying value. The display is just cosmetic.

This is why you can change a cell's format from “Date” to “Number” and suddenly see 45672 instead of January 15. The value didn't change — just how Excel is showing it to you.

Essential Date Functions

Excel has dozens of date functions. Here are the ones you'll use most often:

TODAY() — Returns today's date as a serial number. Updates automatically when the workbook recalculates.

NOW() — Returns the current date and time. Also updates automatically.

DATE(year, month, day) — Creates a date from separate year, month, and day values. Useful when you have dates split across columns.

YEAR(), MONTH(), DAY() — Extract components from a date. YEAR(A1) gives you just the year.

EOMONTH(start_date, months) — Returns the last day of a month, offset by a number of months. EOMONTH(A1, 0) gives the last day of A1's month. EOMONTH(A1, 1) gives the last day of the next month.

NETWORKDAYS(start, end) — Counts working days between two dates, excluding weekends. There's also NETWORKDAYS.INTL for custom weekend definitions.

DATEDIF(start, end, unit) — Calculates the difference between dates in years, months, or days. This function is undocumented but still works. Units are “Y”, “M”, or “D”.

Date Arithmetic

Because dates are numbers, math works directly:

Adding days: =A1+30 adds 30 days to the date in A1.

Subtracting dates: =B1-A1 gives the number of days between two dates.

Adding months: Use EDATE(A1, 2) to add 2 months. Simple addition doesn't work for months because months have different lengths.

Adding years: Use DATE(YEAR(A1)+1, MONTH(A1), DAY(A1)) to add a year. Or EDATE(A1, 12).

Common Date Pitfalls

Text that looks like dates. Sometimes what looks like a date is actually text. “1/15/2025” might be a number (45672) or it might be a text string. Text dates won't sort correctly and won't work in date functions. Check by formatting as a number — if it stays as text, it's not a real date.

Regional format confusion. Is “01/02/2025” January 2 or February 1? Depends on your locale settings. When sharing files internationally, use unambiguous formats like “2025-01-02” or “02-Jan-2025”.

Two-digit year interpretation. Excel interprets two-digit years based on a cutoff (usually 30). So “1/1/29” becomes 2029, but “1/1/30” becomes 1930. Always use four-digit years to avoid ambiguity.

Dates imported as text. Data from external sources often comes in as text. Use DATEVALUE() to convert text dates to real dates, or Text to Columns with date formatting.

Time zone issues. Excel has no concept of time zones. If you're working with data from multiple time zones, you need to handle conversions manually.

Official Resources

• Microsoft: Date & Time Functions — Complete reference for all date functions.
• Microsoft: DATEVALUE Function — Convert text to date values.