Day 7 — Working with dates

A date in Excel is just a number wearing a costume. Once you know that, date maths stops being frightening.

A date is a number

Every formula on this page runs against the Sales sheet below. The row numbers and column letters are real — F2 really is 165000.

B2 shows 05-Jan-2025. Underneath, Excel stores 45662 — the number of days since 1 January 1900. That is why you can subtract one date from another and get a sensible answer.

Seven days between orders 1001 and 1002. 158 days from the first order to the last. =B2+30 gives the serial number for thirty days later.

Why dates sometimes appear as 45662 Because the cell is formatted as a number rather than a date. The value is right; only the costume is missing. Format the cell as a date and it reappears.

Pulling a date apart

WEEKDAY returns 1 for Sunday through 7 for Saturday by default. 5 January 2025 was a Sunday, so this returns 1.

Building a date

=DATE(year, month, day)

Month 13 does not fail — it rolls over into January of the next year. That is genuinely useful for stepping through months in a loop.

TEXT — showing a date the way you want

TEXT turns a date into text Once you have used TEXT, the result is no longer a date and you cannot do date maths on it or sort it chronologically. Use TEXT for labels and report headings, never for a column you still need to calculate with.

That last point is exactly what makes =TEXT(B2,"mmm yyyy") so useful for grouping — and so dangerous if you then try to sort by it, because "Apr 2025" sorts before "Jan 2025" alphabetically.

Month-end and month-shifting

=EOMONTH(date, months to shift) end of month =EDATE(date, months to shift) same day, later month

EOMONTH(date,0) is the end of the current month, -1 the end of the previous one. These two are the backbone of every monthly MIS: they give you period boundaries that are correct in February and in leap years without you thinking about it.

DATEDIF — the undocumented one

=DATEDIF(start, end, unit) "Y" whole years "M" whole months "D" days

This lesson uses the Staff sheet below.

Amit joined in April 2019, so by 30 June 2025 he has completed 6 years and 2 months. DATEDIF counts completed periods, which is what a service or age calculation actually needs.

DATEDIF is real, but Excel will not help you type it It is a leftover from Lotus 1-2-3. There is no tooltip and it does not appear in the function list, yet it works in every version. Type it in full.

TODAY

TODAY() changes every day It recalculates each time the file opens. Useful for a live dashboard; wrong for anything you need to reproduce later, such as a saved month-end report. For those, type the date or use DATE().

Grouping sales by month

January 210,000, February 158,000, April 238,600. Combining SUMIFS with DATE and EOMONTH gives you a monthly report that never needs its boundaries adjusted.

Try these yourself

How many days passed between order 1003 and order 1012?
Show the date in B5 as Feb 2025.
Find the last day of the month for order 1009.
Work out how many complete years Sneha Rao had served by 30 June 2025.
Total the March 2025 sales using DATE and EOMONTH.

← PreviousDay 6 — FIND, SUBSTITUTE and splitting text Next →Day 8 — VLOOKUP and why it disappoints