Goal
You’ll be able to use core date and time functions correctly, and understand the genuinely important fact that dates in Excel are actually stored as plain numbers underneath their displayed formatting.
Learn
A genuinely important foundational fact: Excel stores dates internally as plain numbers — specifically, the number of days since a fixed starting reference date (January 1, 1900). What you see displayed as “March 15, 2026” is really just a number like 46091, formatted to display as a readable date. This is exactly why you can subtract one date from another and get a meaningful number of days — you’re really just subtracting two underlying numbers.
=TODAY() (returns today's actual current date) =NOW() (returns the current date AND time) =B1-A1 (if both are dates, returns the number of days between them) =YEAR(A1) (extracts just the year from a date) =MONTH(A1) (extracts just the month number) =DAY(A1) (extracts just the day number)
Because dates are just numbers underneath, you can also do genuinely useful date arithmetic directly:
=A1+30 (returns the date 30 days after whatever date is in A1) =A1+7 (returns the date exactly one week later)
This same underlying-number fact explains a specific common confusion: if a cell containing a date suddenly displays as a plain number like 46091 instead of a readable date, the cell’s formatting was likely changed to General or Number by accident — the underlying value is still correct, but its display formatting needs to be reset back to a Date format to show it properly again.
Decision Task
A cell that previously showed “March 15, 2026” now suddenly displays as the number 46091 after some editing. Before reading on: has the actual underlying date value been lost or corrupted, and what would fix the display?
Show Answer
No, the underlying value hasn’t been lost at all — this is exactly the “dates are really just numbers” fact from this lesson. The cell’s display formatting was likely accidentally changed to General or Number; reapplying a Date format to that same cell (Format Cells → Date) would immediately show the correct readable date again, since the actual underlying number never changed.
Common Mistake
Panicking that a date value has been “lost” or corrupted when a date-formatted cell suddenly shows a plain number instead, without realizing this is almost always just a formatting change, not actual data loss. The underlying number (and therefore the actual date it represents) is still completely intact; only the cell’s display formatting needs to be corrected.
Practice Questions
1. Write a formula that returns today’s current date automatically, updating each time the sheet is opened.
Show Answer
=TODAY()
2. If A1 contains a start date and B1 contains an end date, write a formula that calculates the number of days between them.
Show Answer
=B1-A1
3. Write a formula that returns a date exactly 14 days after whatever date is in cell C1.
Show Answer
=C1+14
4. Why does subtracting one date cell from another correctly return a meaningful number of days?
Show Answer
Because dates are stored internally as plain numbers (days since a fixed reference date), so subtracting two dates is really just subtracting two underlying numbers, which naturally gives the difference in days.
5. What function would extract just the year from a date stored in cell D1?
Show Answer
=YEAR(D1)
Try It Yourself
Without looking back, explain in your own words why a date-formatted cell showing a large plain number (like 46091) instead of a readable date almost never means the actual data was lost.
Show Answer
Dates in Excel are stored internally as plain numbers representing days since a fixed reference date; a cell showing that raw number instead of a formatted date simply means its display formatting was changed away from a Date format, not that the underlying value (and the date it represents) was actually lost or altered.
Quick Check
1. How does Excel actually store dates internally?
Show Answer
As plain numbers, representing the number of days since a fixed reference date (January 1, 1900).
2. What does TODAY() return?
Show Answer
The current actual date, updating automatically.
3. What does NOW() return, differently from TODAY()?
Show Answer
The current date AND time, not just the date.
4. Why can you meaningfully subtract one date cell from another?
Show Answer
Because dates are really just numbers underneath, so subtracting two dates is really subtracting two numbers, correctly giving the number of days between them.
5. If a date cell suddenly shows a large plain number instead of a readable date, what has most likely happened?
Show Answer
The cell’s display formatting was changed away from Date format, not that the actual underlying value was lost.