Dates and Times in Excel: The Complete Guide

Almost every Excel date problem — the formula that returns #VALUE!, the column that won’t sort, the “date” that’s secretly text — traces back to one fact most users never learn: Excel doesn’t store dates as dates. It stores them as numbers. Once you understand that, everything else (formatting, date math, working-day calculations, duration tracking) falls into place. This guide covers the whole system, from how dates are stored to the functions you’ll actually use, with links to step-by-step tutorials for each task. Keep the date functions cheat sheet handy as a quick-reference companion.

How Excel Stores Dates: Serial Numbers

Every date in Excel is a serial number counting days since January 1, 1900 (serial number 1). January 2, 1900 is 2, and June 11, 2026 is 46,184. Times are decimal fractions of a day: 0.5 is noon, 0.25 is 6:00 AM, and 0.75 is 6:00 PM. A combined date-time like June 11, 2026 3:00 PM is simply 46184.625.

Prove it to yourself: type a date into a cell, then press Ctrl+Shift+~ to apply the General format. The date “disappears” and the serial number shows — the underlying value never changed, only the display. Press Ctrl+1 and reapply a date format to get it back.

This design has three consequences that drive everything in this guide:

Consequence	What it means in practice
Dates are numbers	You can add, subtract, average, and compare them: `=B2-A2` returns days between two dates
Formatting is cosmetic	`6/11/2026`, `11-Jun-26`, and `46184` can all be the same cell value
Text that looks like a date isn’t one	`"06/11/2026"` stored as text won’t sort or calculate until converted

One historical wart: Excel treats 1900 as a leap year (it wasn’t), so serial number 60 corresponds to the nonexistent February 29, 1900. This bug was carried over deliberately from Lotus 1-2-3 for compatibility. It only matters if you work with dates in January–February 1900, which in practice means it never matters — but it explains why WEEKDAY results before March 1, 1900 are off by one.

Dates before 1900 have no serial number at all, which is why typing 5/14/1843 gives you left-aligned text, not a date. Genealogists and historians need workarounds (store year/month/day in separate columns, or use text functions).

Entering Dates and Times

Excel parses what you type against your system’s regional settings. On a US system, 6/11, 6-11, jun 11, and 6/11/26 all become June 11 of the relevant year. Two keyboard shortcuts are worth memorizing because they insert static values (unlike TODAY and NOW, which recalculate):

Ctrl+; — insert today’s date
Ctrl+Shift+; — insert the current time

Two-digit years follow a cutoff rule: 00–29 become 2000–2029, while 30–99 become 1930–1999. Typing 6/11/29 gives you 2029, but 6/11/30 gives you 1930. When in doubt, type the full four-digit year.

For filling a series — daily, weekdays only, monthly, yearly — drag the fill handle and use Auto Fill Options, or use Home → Fill → Series for precise control. The full techniques are in how to autofill dates in Excel, and if you want dates to appear automatically as you enter data in adjacent columns, see adding dates automatically.

The flip side of Excel’s eager date parsing: it converts things you don’t want converted. Type 3-1 as a part number and you get March 1. Type the gene name MARCH1 into older Excel versions and it became a date (a problem so severe that geneticists renamed the gene). The fixes — leading apostrophe, pre-formatting the column as Text, or import settings — are covered in how to make Excel stop changing numbers to dates.

Formatting Dates and Times

Number formats change how a value displays, never what it is. Press Ctrl+1 → Number tab → Date (or Custom) to control display. The built-in Short Date format is one click away on the Home tab — see applying the short date format — and changing date formats walks through the full dialog.

Custom format codes are where the real power is:

Code	June 11, 2026 displays as
`m/d/yyyy`	6/11/2026
`dd-mmm-yy`	11-Jun-26
`mmmm d, yyyy`	June 11, 2026
`ddd`	Thu
`dddd, mmmm d`	Thursday, June 11
`yyyy-mm-dd`	2026-06-11 (ISO 8601 — sorts correctly as text too)

For times: h:mm AM/PM, hh:mm:ss, and the critical bracket codes [h]:mm and [mm]:ss, which display elapsed time beyond the normal rollover. Format 26:30 of accumulated hours as h:mm and you’ll see 2:30 (it rolled past 24); format it as [h]:mm and you see 26:30. This single distinction resolves most “my timesheet total is wrong” complaints.

Two formatting gotchas:

A date format applied to text does nothing. If Ctrl+1 doesn’t change how the cell looks, the value is text, not a date — jump to the text dates section below.
m means months in date formats but minutes when it follows h or precedes ss. h:mm is hours and minutes; m/d is month and day. Excel decides by context.

To visually flag dates — overdue items in red, this week’s deadlines in yellow — use conditional formatting with rules like “Cell value < TODAY()”. And when a date column refuses to sort chronologically, that’s a value problem (text dates), not a sorting problem.

The Core Date and Time Functions

These fifteen functions cover 95% of real-world date work. Each links to a full tutorial with examples.

Function	Syntax	Returns
TODAY	`=TODAY()`	Current date (volatile — updates on recalc)
NOW	`=NOW()`	Current date and time (volatile)
DATE	`=DATE(year, month, day)`	A real date built from three numbers
YEAR	`=YEAR(date)`	The year as a number (e.g., 2026)
MONTH	`=MONTH(date)`	Month number 1–12
DAY	`=DAY(date)`	Day of month 1–31
DATEDIF	`=DATEDIF(start, end, "unit")`	Difference in years (“y”), months (“m”), or days (“d”)
EDATE	`=EDATE(start, months)`	Date N months before/after
EOMONTH	`=EOMONTH(start, months)`	Last day of a month N months away
WEEKDAY	`=WEEKDAY(date, [type])`	Day-of-week number (1=Sunday by default)
WEEKNUM	`=WEEKNUM(date, [type])`	Week number of the year
NETWORKDAYS	`=NETWORKDAYS(start, end, [holidays])`	Working days between two dates
WORKDAY	`=WORKDAY(start, days, [holidays])`	Date N working days in the future/past
YEARFRAC	`=YEARFRAC(start, end, [basis])`	Fraction of a year between dates
TIME	`=TIME(hour, minute, second)`	A time value from three numbers

Three notes the function reference pages won’t shout at you:

DATEDIF is undocumented but reliable. It doesn’t appear in Excel’s function autocomplete, yet it’s the cleanest way to get whole years/months between dates. Argument order matters: start date first, or you get #NUM!.
DATE self-corrects out-of-range arguments, which is a feature, not a bug: =DATE(2026, 14, 1) returns February 1, 2027. This makes month arithmetic trivial inside formulas.
TODAY and NOW are volatile — they recalculate every time the workbook calculates. Fine in small files; in a 100,000-row model, put =TODAY() in one named cell and reference it everywhere instead of repeating it.

If your schedule uses non-standard weekends (say, Friday–Saturday in the Middle East), NETWORKDAYS.INTL and WORKDAY.INTL accept a weekend parameter. For ISO 8601 week numbering (weeks start Monday, week 1 contains the first Thursday), use ISOWEEKNUM instead of WEEKNUM.

Date Math in Practice

Because dates are serial numbers, arithmetic is direct. Here are the patterns that come up constantly.

Days, months, and years between dates

Plain subtraction gives days: =B2-A2. If the result shows as a date like 1/30/1900, Excel guessed the format — press Ctrl+Shift+~ to see the number. Full walkthrough: calculating days between dates.

For months and years, DATEDIF’s units do the work — "m" for complete months, "ym" for months ignoring years, "md" for days ignoring months and years. See calculating months between two dates for examples of each unit.

Calculating ages and tenure

The canonical age formula:

=DATEDIF(birthdate, TODAY(), "y")

And the “X years, Y months, Z days” version:

=DATEDIF(A2,TODAY(),"y")&" years, "&DATEDIF(A2,TODAY(),"ym")&" months, "&DATEDIF(A2,TODAY(),"md")&" days"

Step-by-step variations are in how to calculate age in Excel, and the same pattern computes employee tenure — see calculating years of service. Avoid =(TODAY()-A2)/365.25 for ages; it drifts near birthdays. DATEDIF counts actual calendar years.

Deadlines and recurring dates

N days out: plain addition — =A2+30 for net-30 invoice terms. More patterns in adding days to a date.
N months out (same day-of-month): =EDATE(A2, 3) for a quarterly renewal. EDATE handles short months sensibly: 3 months after January 31 is April 30, not a phantom April 31. See adding months to a date.
N years out: =EDATE(A2, 12*N) or the DATE-function approach in adding years to a date — but beware =DATE(YEAR(A2)+1, MONTH(A2), DAY(A2)) on February 29, which rolls to March 1.
End-of-month deadlines: =EOMONTH(A2, 0) is the last day of A2’s month; =EOMONTH(A2, 0)+1 is the first day of the next month — the cleanest “first of next month” formula in Excel.
First/last specific weekday of a month: =EOMONTH(A2,-1)+1+MOD(6-WEEKDAY(EOMONTH(A2,-1)+1,2),7) returns the first Saturday; adjust the 6 for other days. WEEKDAY’s return-type argument (use type 2 for Monday=1) does the heavy lifting here.

Working days and business deadlines

=NETWORKDAYS(A2, B2) counts Monday–Friday days inclusive of both endpoints. The third argument takes a range of holiday dates — keep a named range like Holidays on a settings sheet and every formula in the workbook respects it:

=NETWORKDAYS(A2, B2, Holidays)

To project a deadline rather than count between dates, use WORKDAY: =WORKDAY(TODAY(), 10, Holidays) returns the date 10 business days from now, skipping weekends and your holiday list. Note the asymmetry people miss: NETWORKDAYS is inclusive of the start date, WORKDAY is exclusive — =WORKDAY(A2,1) is the next working day, not A2 itself.

For countdowns, =B2-TODAY() gives days remaining; wrap it as =MAX(0, B2-TODAY()) to stop at zero, and pair it with conditional formatting to highlight rows approaching their deadline.

Times and Durations

Times follow the same serial-number logic at smaller scale: 1 hour = 1/24 ≈ 0.041667, 1 minute = 1/1440, 1 second = 1/86400.

Basic time difference: end minus start. =B2-A2 formatted as h:mm gives hours worked. Full setup including breaks: calculating hours worked and calculating time differences.

Overnight shifts: when a shift starts at 10:00 PM and ends at 6:00 AM, plain subtraction goes negative (and displays as #####). The standard fix:

=MOD(B2-A2, 1)

MOD wraps the negative result around midnight, returning 8 hours correctly whether or not the shift crosses midnight.

Converting durations to decimal hours for payroll: a time value of 8:30 is 0.354167 as a number, not 8.5. Multiply by 24 — =(B2-A2)*24 — and format the result as a plain number. This trips up more timesheet builders than any other single issue; details in converting time to decimal. The same logic in reverse: divide decimal hours by 24 to get a time value, and use the [h]:mm bracket format for totals over 24 hours.

Adding time: =A2+TIME(1,30,0) adds 1 hour 30 minutes; for amounts over 23 hours, add the fraction directly (=A2+25/24). See adding hours and adding minutes for both approaches.

Military time: =TEXT(A2,"h:mm AM/PM") for display, or proper value conversion in converting military time to standard time.

To extract components from any time, HOUR and MINUTE work exactly like YEAR and MONTH do for dates.

Common Date Problems (and How to Fix Them)

Problem 1: Dates stored as text

The single most common date issue. Symptoms: values are left-aligned, sorting groups them alphabetically (all the 1s together, then 10, 11, 12…), formulas return #VALUE!, and Ctrl+1 formatting does nothing. A quick test: =ISTEXT(A2).

Fixes, fastest first:

Find & Replace trick: replace / with / (yes, the same character). The re-entry forces Excel to reparse each cell, converting recognizable text dates in one pass.
DATEVALUE: =DATEVALUE(A2) converts a text date string to a serial number — full details in the DATEVALUE tutorial (TIMEVALUE is its twin for text times).
Text to Columns: select the column → Data → Text to Columns → Finish (choosing the right date order in step 3 if needed). Converts in place, no helper column. The complete workflow is in converting text to dates.
Surgical parsing for formats DATEVALUE can’t read — e.g., the 8-digit 20260611 becomes a real date with =DATE(LEFT(A2,4), MID(A2,5,2), RIGHT(A2,2)). Walkthrough: converting 8-digit numbers to dates.

Problem 2: US vs. European date formats

03/04/2026 is March 4 in New York and April 3 in London. When data crosses that boundary, you get the nastiest failure mode in Excel: silent partial corruption. Days 1–12 convert (wrongly — months and days swapped), while days 13–31 stay as text because “month 13” doesn’t parse. Half your column is wrong dates, half is text, and nothing errored.

Defenses:

On import (CSV/text files), always declare the source order: Text to Columns step 3 → Column data format → Date → pick DMY or MDY. Power Query (Data → Get Data) does the same via its locale setting — right-click the column → Change Type → Using Locale.
Auditing existing data: if =ISTEXT(A2) returns TRUE for some rows in a “date” column, suspect a format-order import accident. Convert the text rows with =DATE(RIGHT(...), ...) style parsing, then check whether the already-converted rows have day and month swapped: =DATE(YEAR(A2), DAY(A2), MONTH(A2)) swaps them back.
For storage and exchange, use ISO 8601 (yyyy-mm-dd). It’s unambiguous in every locale and sorts correctly even as text.

Your own machine’s parsing order follows Windows regional settings (Control Panel → Region), not an Excel option — changing it affects how Excel interprets everything you type.

Problem 3: The 1900 vs. 1904 date systems

Classic Mac Excel counted dates from January 1, 1904 instead of 1900. Modern Excel on both platforms defaults to 1900, but old Mac workbooks may still carry the 1904 setting (File → Options → Advanced → “Use 1904 date system”). When data is copied between workbooks using different systems, every date shifts by exactly 4 years and 1 day (1,462 days).

If all dates in a pasted range are uniformly off by about four years, this is why. Fix: add or subtract 1462 in a helper column (=A2+1462 going from 1904-based to 1900-based), paste as values, done. Better: check the setting before merging workbooks, and never enable 1904 in new files. One legitimate historical use of the 1904 system — it allowed negative time display — is now better handled with [h]:mm formats or MOD.

Problem 4: Date and time mashed together

Timestamps like 6/11/2026 14:32 break daily grouping, VLOOKUP matches against plain dates, and COUNTIFS by day. Strip the time with =INT(A2) (the integer part is the date; the fraction is the time) — more options in removing time from dates. Going the other direction, separating date and time into two columns and combining them back (=date_cell + time_cell — it’s literally addition) are both one-formula jobs.

Putting It Together: A Deadline Tracker Pattern

A pattern that exercises most of this guide in five columns — adapt it for invoices, projects, or contract renewals:

Column	Formula	What it does
Start date	(entered)	Real date, validated with Data Validation → Date
Due date	`=WORKDAY(A2, 15, Holidays)`	15 business days out, holiday-aware
Days left	`=B2-TODAY()`	Negative = overdue
Status	`=IF(C2<0,"OVERDUE",IF(C2<=5,"Due soon","On track"))`	Drives conditional formatting
Week	`=ISOWEEKNUM(B2)`	For grouping in pivot reports

Format the due-date column ddd, mmm d so the weekday is visible at a glance, and add a conditional formatting rule on =$C2<0 to flag overdue rows red.

FAQ

Why does my date formula show a number like 46184 instead of a date?

The formula returned a date serial number, but the cell carries the General format. The value is correct — press Ctrl+1 and apply a date format. The reverse also happens: subtracting two dates sometimes inherits a date format and shows 2/8/1900 instead of 39 days. Same fix, opposite direction: format the cell as a number.

What’s the difference between DATEDIF and just subtracting dates?

Subtraction (=B2-A2) returns total days — perfect for countdowns and day counts. DATEDIF returns calendar-aware differences: complete years, complete months, or mixed units, which is what you want for ages, tenure, and anniversaries. Dividing days by 365.25 approximates years but gets ages wrong near birthdays; DATEDIF doesn’t.

How do I sum times beyond 24 hours without losing hours?

Use the elapsed-time format [h]:mm on the total cell (Ctrl+1 → Custom). Without brackets, h:mm rolls over at 24, so 30 hours displays as 6:00. For payroll math, convert to decimal hours instead: multiply the time total by 24 and format as a number.

Why did all my dates shift by exactly 4 years?

The data moved between a workbook using the 1904 date system (legacy Mac default) and one using the 1900 system. Each date is off by 1,462 days. Add or subtract 1462 in a helper column to correct, then disable the 1904 setting (File → Options → Advanced) in the offending workbook.

Excel keeps turning my product codes into dates. How do I stop it?

Pre-format the column as Text before typing or pasting, prefix individual entries with an apostrophe ('3-1), or use Data → From Text/CSV and set the column type to Text during import. All three methods, plus undoing damage already done, are covered in how to stop Excel changing numbers to dates.