Excel’s Secret About Dates

Ever stared at an unformatted date in Excel (or Google Sheets) and wondered what magical incantation makes it tick? Well, prepare to have your minds slightly blown (or at least mildly amused), because we’re about to uncover a secret that’s been hiding in plain sight: Dates are just numbers in fancy dress!

The Secret Identity: Every Date is an Integer!

That’s right! Behind every seemingly innocent “01/06/2026” is a humble integer. Think of it like this: Excel started counting on January 1, 1900, and called it ‘1’. January 2, 1900? That’s ‘2’. Fast forward to May 10, 2025, and it’s actually the number… well, it’s a big number (it’s 45787, if you must know!).

“Do I Have to Type Numbers Like a Robot?” Heck No!

Now, before you start meticulously calculating the integer for your grandma’s birthday, RELAX. You absolutely do not need to enter dates and times as their numerical alter egos. Just type them in like a normal human:

• DD.MM.YYYY (e.g., 01.06.2026)
• DD/MM/YYYY (e.g., 01/06/2025)
• YYYY-MM-DD (e.g., 2024-06-01) – My personal favorite for less ambiguity!
• YYYY/MM/DD (e.g., 2025/06/01)
• DD month name YY (e.g., 1 June 25) – Casual, yet effective.
• DD short month designation YY (e.g., 1 Jun 2025)

Excel and Google Sheets are smart cookies; they’ll see what you’re doing and slap that “Date” format on it faster than you can say “pivot table.”

“Ack! My Dates Are Numbers!” Panic Attack

So, why spill the beans on this numerical conspiracy? Because one day, you’ll open a spreadsheet, and where you expect to see lovely, orderly dates, you’ll find a column of bewildering numbers (like our friend 45787). DON’T PANIC! Your dates haven’t eloped with a rogue algorithm. It’s just a formatting snafu. Select the cells, right-click, choose “Format Cells,” and tell Excel to treat them like the dates they truly are. Phew!

And because dates are undercover numbers, you can unleash the power of arithmetic! at you’re doing and slap that “Date” format on it faster than you can say “pivot table.”

Dates in Formulas: Shaken, Not Stirred

You can use dates in formulas in two main ways:

1. As constants: Type them directly into the formula, but wrap them in quotation marks like a precious gift: "01/01/2024", "2026-01-01".
2. As cell references: If your date is chilling in cell A1, just use A1 in your formula (no quotes needed, it’s not shy).

Then, the math magic:

• Subtract one date from another: ="15/05/2025"-"10/05/2025" or B1-A1 will give you the difference in days (in this case, 5).
• Add days to a date: ="10/05/2025"+7 or A1+7 will give you the date 7 days later.

Staying Current: TODAY() and NOW() – Your Dynamic Duo!

Need the current date? BAM! =TODAY()

Need the current date AND time? KAZAM! =NOW()

These functions are argument-free (their parentheses are emptier than my snack drawer on a Monday morning) because, well, today is always today. But you still need those empty parentheses () to signal to Excel that it’s a function, not you trying to type the word “TODAY” in shouty caps.

Warning: These are “volatile” functions. Like a moody teenager, they recalculate every single time anything changes in your workbook. Great for always being up-to-date, not so great if you want to stamp a date and have it stay put.

Freezing Time: Static Date/Time Shortcuts

Want to log the exact moment you finished that soul-crushing report? You need static dates/times that don’t change. Excel has some nifty keyboard shortcuts for this (assuming an English keyboard layout):

• Current Date (static): Ctrl + ; (semicolon)
• Current Time (static): Ctrl + Shift + ; (semicolon)
• Date and Time in one cell (static): Ctrl + ; then space then Ctrl + Shift + ;

(If you’re rocking a different keyboard layout, these might differ – Google is your friend here!)

DATEDIF: The Secret Agent of Date Differences

Sometimes, “days” isn’t enough. You need to know age in years, or months between project milestones. Subtracting dates gives you days, but for more complex intervals, meet DATEDIF. This function is so undercover, Excel often won’t even show you its arguments as you type it, but trust us, it works!

=DATEDIF(start_date, end_date, unit)

• start_date: Your beginning date (in quotes, a cell reference, or even TODAY()).
• end_date: Your, well, end date.
• unit: The magic happens here (use quotes!):
  • "d": Total days (but simple subtraction is easier, just sayin’).
  • "m": Total full months.
  • "y": Total full years. (Perfect for =DATEDIF(B2, TODAY(), "y") to calculate age from a birthdate in B2).
  • "md": Days, ignoring months and years (e.g., difference between Jan 1st and Feb 15th is 14, ignoring that they are in different months).
  • "ym": Months, ignoring days and years (e.g., difference between Jan 1st, 2025 and June 15th, 2026 is 5 months).
  • "yd": Days, ignoring years (e.g., difference between Jan 1st, 2024 and June 15th, 2025 is 166 days, counting from Jan 1st of the end year).

Google Sheets is a bit more forthcoming and will show DATEDIF arguments. Thanks, Google!

Dissecting Dates: The Autopsy Functions

Want to pull out just one piece of a date? These functions return numbers, not new dates (except EOMONTH, that rebel).

• =DAY("01/07/2025") → 1 (Gives you the day number)
• =MONTH("01/07/2025") → 7 (Gives you the month number)
• =YEAR("01/07/2025") → 2025 (Gives you the year)
• =EOMONTH("01/07/2025", 0) → 31/07/2025 (Date of the last day of the current month)
• =EOMONTH("01/07/2025", 1) → 31/08/2025 (Date of the last day of the next month)
• =WEEKNUM.ISO("01/01/2025") or =ISOWEEKNUM("01/01/2025") → 1 (Week number, ISO standard where the first week has the year’s first Thursday).
• =WEEKNUM("01/01/2027", 2) → 1 (Week number, second argument 2 means week starts on Monday).
• =WEEKDAY("01/07/2025", 2) → 2 (Day of the week as a number. 2 means Monday is day 1, Tuesday is day 2, etc. If you omit the 2, Sunday is day 1, like some kind of savage).

Want the day of the week as glorious text, like “Tuesday”?
Use the TEXT function: =TEXT("01/07/2025", "DDDD") → Tuesday (or DDD for “Tue”).

The Quarter Conundrum: Where’s QUARTER()?

You’d think there’d be a simple QUARTER() function, right? WRONG. Excel and Google Sheets left that one out, probably just to watch us squirm. But fear not, a common workaround using the month number is:
=ROUNDUP(MONTH(A1)/3,0) or =INT((MONTH(A1)+2)/3)

(Assuming your date is in A1). This little formula heroically calculates the quarter for you.

Phew! So there you have it. Dates in spreadsheets are more than just pretty characters; they’re numerical powerhouses in disguise, ready for your calculations, manipulations, and general data wizardry. Go forth and conquer your calendars!