Dates Are Numbers
Why Excel dates sort, subtract and sometimes lie: a self-study lesson for everyday council work.
How this deck works
Driving it
Click (or → / Space) builds the current slide one step at a time, then moves on
← undoes one step, then one slide
E edits any text in place (Ctrl+S saves a copy)
Rail (left) jumps between the 7 sections
The colour key, worth memorising
Prerequisite knowledge
Writing a simple formula
=D2-C2
You'll need to be comfortable writing a small formula with cell references for this part. If that's unfamiliar, work through the Your First Formulas deck, then come back.
Format Cells
right-click a cell → Format Cells (Ctrl+1)
You'll need to know that this dialog exists and how to open it. We'll use it to change how a cell is displayed; if you've never opened it, try it once now.
Meet the two datasets
Both sheets live and die by their dates. If you can make dates behave on both, you own this skill; that transfer is the test this deck keeps setting you.
5 requests across the communities · logged date and due date for each.
4 staff · start date and days away. The Back column is empty; we'll build it.
Every date is wearing a costume
Here is the secret this whole deck rests on. Pick a date, any date, and strip its formatting away: right-click, Format Cells, choose General. What's underneath isn't a date at all.
A counter that started in 1900
Excel numbers every single day. Day 1 was Monday, 1 January 1900, and the counter has ticked up by exactly one per day ever since. That's the entire system; there is nothing else to learn about what a date is.
Subtract two dates, count the days
Because dates are numbers, ordinary subtraction just works. "How long does each job get?" is one small formula: due date minus logged date. Excel quietly does 46223 − 46209 and hands you the gap in days.
Add days to a date, get a date
It works the other way too. Start plus days away gives the first day back, because adding 7 to a day number lands you exactly one week down the calendar. Let's fill the leave planner's empty column.
Why real dates sort perfectly
Sort the log oldest to newest and it just works. No magic: Excel is sorting the day numbers, smallest to largest, and the costumes come along for the ride.
The impostor: text dressed as a date
Not everything that reads like a date is one. Type 6.7.26 with full stops, or paste dates in from an email or an old system, and Excel shrugs and stores plain text. To your eye they're near-identical. Excel gives you one quiet tell.
What sorting does to text dates
Here's the same five days twice: once as real dates, once as typed text. Sort both ascending. Text sorts like words in a dictionary, character by character, so everything starting with "1" clumps together and August lands before February.
One number, many faces
Since the value and its look are separate, one day number can wear any face you like. Every line below is the same cell, the same 46248, dressed four different ways through Format Cells.
Typing a date Excel understands
Whether you get a real date or an impostor is decided at the moment you press Enter. The rule on an Australian-set computer: day, month, year, separated by slashes. Then watch which way the cell leans.
Two traps, and the format that never lies
Two quiet ways a typed date can betray you, and the one way of writing dates that survives every computer, every region and every sort, even if it accidentally ends up as text.
3/7/2026: March or July?
On an Australian-set machine that's 3 July. On a US-set machine the same keystrokes mean March 7. Share the file and the meaning can silently flip.
The 1930 surprise
Two-digit years get guessed: 00 to 29 land in the 2000s, 30 to 99 land in the 1900s. Type 14/8/30 and you've written 1930. Always type the full year.
2026-08-14
Year, month, day. Excel reads it correctly in any region, no digit can be misread, and because the biggest unit comes first it even sorts correctly as plain text. The one format that never lies.
Repairing a column of impostors
You've inherited a sheet and the dates lean left. Here's the drill, from quickest to most thorough.
Confirm the diagnosis
Left-aligned entries, a sort that scrambles, or a subtraction that throws #VALUE!: any one of these means text.
A few cells: retype
Click the cell, type the date properly with slashes, press Enter, watch it jump right. For a handful of impostors this is the fastest honest fix.
A whole column: convert
Select the column, then Data → Text to Columns → Next, Next → Date: DMY → Finish. Excel re-reads every cell as a day-month-year date in one pass.
The cheat sheet, keep this one
Date maths
| =D2-C2 | days between two dates |
| =C2+14 | a fortnight after C2 |
| =TODAY() | today, always current |
Typing dates
- Slashes, not full stops: 14/8/2026
- Always a four-digit year
- Shared files: 2026-08-14
- After Enter it should jump right
The tells
| right | real date, safe to trust |
| left | text impostor |
| sort odd | text lurking in the column |
| #VALUE! | maths touched a text date |
When it goes wrong: the usual suspects
#####
A row of hashes where the date should be. Nothing is wrong with the value; the column is too narrow for the costume.
Fix: double-click the boundary between the column letters.
#VALUE!
A subtraction or addition touched a cell that only looks like a date.
Fix: find the left-aligned impostor and retype it with slashes.
It shows 46248
The maths worked but the answer came out as a plain number: a date that lost its costume.
Fix: the value is right. Ctrl+1, choose a Date format, done.
14.8.26 just sits there
Full stops aren't date separators, so Excel stored your keystrokes as text.
Fix: retype with slashes: 14/8/2026.
The 1930 surprise
A two-digit year of 30 or more quietly lands in the 1900s.
Fix: always type the full four-digit year.
Golden rule
Right-aligned and it recalculates: a real date. In doubt? Subtract the cell from itself: a real date gives 0, an impostor gives #VALUE!.
Now teach it
Your six talking points
- A date is a day number: the count of days since 1 January 1900.
- The format is a costume: it changes the look, never the value.
- The alignment tell: real dates lean right, text impostors lean left.
- Date maths is plain maths: subtract to count days, add to move deadlines.
- Sorting sorts the numbers: which is why text dates scramble.
- Type slashes and full years, and use year-month-day for anything shared.
Three challenges, click to reveal each answer
Underneath every tidy date is a number, counting quietly since 1900. Now you can see it.
