Dates & Times

Imagine a huge cloakroom where every single day since 1 January 1900 is handed a ticket number. That very first day got ticket 1. The next day, 2. Keep counting, one ticket per day, and today’s ticket is 46,241 (that is 7 August 2026). This is Excel’s biggest, best-kept secret: a date is not really a date — it is just a number. When you type 07-08-2026, Excel quietly stores the plain number 46,241 and simply shows it to you dressed up as a date.

Why does this matter so much? Because numbers are easy to do sums with. If your EMI is due on ticket 46,260 and today is ticket 46,241, then “how many days until my EMI?” is just 46260 - 46241 = 19 days. No counting on your fingers across the calendar — one number minus another. Every clever date trick in Excel grows from this single idea.

What about the clock? A time is simply a slice of one day. A whole day is 1, so half a day — noon — is 0.5. Six in the morning is 0.25 (a quarter of the way through the day) and six in the evening is 0.75. So a full date-and-time such as 7 Aug 2026 at 6 pm is stored as 46241.75: the whole-number part is the day, and the bit after the decimal point is the time.

Because of this, two things are always true, and they will rescue you again and again. First, you can subtract two dates to get the days between them, and add a number to a date to jump forward in time. Second, when a date looks wrong, it is almost never the number that is broken — it is only the outfit (the format) it is wearing. Learn to see the ticket number hiding under every date, and this whole topic melts into easy arithmetic.

Two quick maps: a mind-map of everything dates and times can do, and a flowchart that points you to the exact function for the job in front of you.

1 · The live clock: TODAY and NOW. =TODAY() hands back today’s date and nothing else — empty brackets, no arguments needed. =NOW() gives the date and the time. Both are volatile: they refresh every time the sheet recalculates, so a cell holding =TODAY() is always “today”. Because a date is a number, =A2-TODAY() tells you how many days until the EMI in A2, and =TODAY()+30 is the date thirty days from now.

2 · Building and splitting dates. To assemble a date from three separate numbers, use =DATE(year, month, day) — for example =DATE(2026, 8, 7) returns 7 August 2026. It is wonderfully forgiving: =DATE(2026, 13, 1) simply rolls over to January 2027, which makes month arithmetic painless. To pull a date apart, three twins do the reverse — =YEAR(A2), =MONTH(A2) and =DAY(A2). For the clock, =HOUR(A2) and =MINUTE(A2) (plus =SECOND(A2)) read the time slice.

3 · Which weekday is it? WEEKDAY. =WEEKDAY(date, 2) turns a date into a day number. That second argument is the counting style: use 2 so Monday is 1 and Sunday is 7, which is how most of us think. It is the key to spotting weekends — a result of 6 or 7 means Saturday or Sunday, which is exactly how attendance and roster sheets flag a non-working day.

4 · Days between — just subtract. Because dates are numbers, the gap between two of them is plain subtraction: =B2-A2 gives the days from the date in A2 to the one in B2. Excel also spells this out as =DAYS(end, start) — mind the order, end first. Invoice ageing is exactly this idea: =TODAY()-InvoiceDate is how many days old a bill is, ready to sort into 30-, 60- and 90-day buckets.

5 · DATEDIF — the hidden gem for age and tenure. Subtraction gives raw days, but for whole years and months — a person’s age, an employee’s tenure — reach for =DATEDIF(start, end, "Y"). It is the one function Excel hides: it never appears as you type and it is missing from the function list, yet it works perfectly if you spell it out in full. The third argument is a unit, in quotation marks:

Stack all three for a friendly age: whole years with "Y", plus the spare months with "YM", plus the spare days with "MD" — giving “36 years, 4 months, 12 days”. Always put the earlier date first, or DATEDIF returns an error.

6 · Jumping whole months: EDATE and EOMONTH. For EMI due dates, =EDATE(start, 1) lands on the same day one month later, and =EDATE(start, 12) a full year on — it handles month lengths for you, so 31 Jan steps sensibly to 28 Feb. =EOMONTH(start, 0) returns the last day of this month; change the 0 to 1 for the end of next month. Salary runs and GST deadlines that fall on a month-end are one EOMONTH away.

7 · Working days only: NETWORKDAYS and WORKDAY. =NETWORKDAYS(start, end, holidays) counts the working days between two dates, automatically skipping every Saturday and Sunday, and skipping any dates in the optional holidays range too. Turn it around with =WORKDAY(start, 10, holidays), which answers “what date is 10 working days from now?” — perfect for delivery promises and project deadlines. Point the holidays argument at a little list of festival dates (Diwali, Holi, Republic Day) and both functions will respect them.

8 · Formatting — same number, different outfit. The value never changes; only its costume does. A format code of dd-mm-yyyy shows 07-08-2026 the British and Indian way, while mm-dd-yyyy (the US style) shows the very same day as 08-07-2026. This is why a date can look “wrong”: nothing is broken, the cell is merely wearing a different format.

9 · Turning text that looks like a date into a real date: DATEVALUE. Dates pasted from a website, a bank statement or a CSV often arrive as text — they sit on the left of the cell and refuse to do any maths. =DATEVALUE("07-08-2026") reads that text and returns the real serial number, which you then format as a date.

Almost every date headache comes from a handful of look-alikes. Learn to spot these and your dates will stop misbehaving.

Type a formula and press Run - the cell turns green the instant it matches. Remember the big idea: a date is just a number, so we add, subtract, count and average those numbers exactly like any others. (This sandbox uses the number side of dates; TODAY, DATEDIF and NETWORKDAYS live in the lesson above.)