Excel Date Functions: TODAY, DATEDIF, EOMONTH and WORKDAY Explained

Excel stores every date as a plain number counting days since 1 January 1900, which is exactly why date maths works at all — adding 30 to a date genuinely adds 30 days, because underneath the formatting, it's just addition. TODAY returns the current date, DATEDIF calculates the difference between two dates in years, months, or days, EOMONTH finds the last day of a month, and WORKDAY adds business days while skipping weekends.

For a long while I built a monthly roster manually, typing in the end-of-month date by hand every single time — 31 one month, 30 the next, 28 or 29 in February depending on the year. It never occurred to me this could be a formula rather than something I just had to remember and retype correctly each month, until a colleague building something similar mentioned EOMONTH in passing, like it was the most obvious thing in the world. I'd been manually counting on a desk calendar for roughly four years before that conversation.

Why do Excel dates behave like numbers sometimes?

This underlies everything else in this post, so it's worth understanding directly. A date like 04/10/2026 is actually stored internally as a serial number — in this case, the count of days since 1 January 1900. This is precisely why you can add or subtract a plain number to or from a date and get a sensible result: adding 7 to a date moves it forward exactly one week, because you're adding 7 to the underlying number, which Excel then displays back in date format.

=A2+30

If A2 is a due date, this returns the date exactly 30 days later, formatted as a date automatically because the original cell was formatted that way.

How does TODAY work, and why does it keep changing?

=TODAY() returns the current date, and recalculates automatically every time the workbook opens or recalculates, rather than staying fixed at whatever date you first entered the formula.

This is genuinely useful for anything that needs to always reflect "right now" — calculating someone's current age, flagging overdue invoices relative to today, or showing how many days remain until a deadline:

=B2-TODAY()

If B2 holds a deadline date, this returns the number of days remaining (or a negative number, if the deadline has already passed).

If you need a date that stays fixed rather than recalculating, use Ctrl+; to insert today's date as a static value instead — this is covered properly in my post on "keyboard shortcuts" .

How does DATEDIF calculate someone's age or tenure?

DATEDIF calculates the difference between two dates in whatever unit you specify — years, months, or days.

=DATEDIF(start_date, end_date, unit)

Calculating someone's age in complete years, based on their date of birth in A2:

=DATEDIF(A2, TODAY(), "Y")

Calculating tenure in complete months:

=DATEDIF(A2, TODAY(), "M")

The unit argument accepts "Y" (complete years), "M" (complete months), "D" (total days), and a couple of more specific combinations like "YM" (remaining months after whole years) for building a more precise "X years, Y months" style result. One quirk worth flagging: DATEDIF genuinely works correctly, but Excel's own built-in function list doesn't officially document it anymore, left over from an older version — it still functions fine in every current version, just don't expect autocomplete to walk you through it the way it does with most other functions.

How does EOMONTH find the last day of a month?

=EOMONTH(start_date, months)

Finding the last day of the current month:

=EOMONTH(TODAY(), 0)

Finding the last day of next month:

=EOMONTH(TODAY(), 1)

Finding the last day of the previous month:

=EOMONTH(TODAY(), -1)

This is exactly what would've saved me those four years of manually typing in month-end dates for that roster — one formula handles every month correctly, including leap years in February, without needing any special-case logic.

How does WORKDAY calculate a due date while skipping weekends?

=WORKDAY(start_date, days, [holidays])

Calculating a due date exactly 10 business days from today, automatically skipping Saturdays and Sundays:

=WORKDAY(TODAY(), 10)

The optional third argument lets you feed in a list of public holiday dates to skip as well, which WORKDAY otherwise has no way of knowing about on its own:

=WORKDAY(TODAY(), 10, HolidayList)

Where HolidayList refers to a range of cells containing known public holiday dates. Without this third argument, WORKDAY only accounts for weekends, not public holidays, which is an easy detail to miss if you're expecting it to be smarter than it actually is by default.

How do I filter or sum data based on a date range?

If you've read my post on "SUMIF, COUNTIF and SUMIFS"  , you'll remember summing values between two dates using two conditions on the same column. The same logic applies directly here, since date ranges are really just number ranges underneath:

=SUMIFS(D2:D200, C2:C200, ">="&F1, C2:C200, "<="&F2)

Where F1 and F2 hold your start and end dates. This only works correctly if the date column is genuinely formatted as dates rather than text —"cleaning messy data", text-formatted dates are exactly the kind of invisible formatting mismatch that silently breaks comparisons like this one.

FAQ

Why does my date formula show a number like 46569 instead of an actual date? This means the cell's formatting is set to General or Number rather than Date. Right-click the cell, choose Format Cells, and select a date format — the underlying value is already correct, it's just being displayed as the raw serial number rather than a readable date.

Can DATEDIF return a negative result? No — if the end date is earlier than the start date, DATEDIF returns a #NUM! error rather than a negative number, which catches people out if the two dates are accidentally entered in the wrong order.

Does WORKDAY account for different countries' public holidays automatically? No, it has no built-in awareness of any holidays at all — you need to supply your own list of relevant dates as the third argument, as shown above, tailored to whichever country or region's holidays matter for your situation.

What's the difference between EOMONTH and just adding days manually? Manually adding a fixed number of days doesn't account for months having different lengths, so it'll drift incorrectly across months with 28, 29, 30, or 31 days. EOMONTH calculates the actual correct end date for any given month, including leap years, without needing separate logic for each case.

Can I calculate someone's age including months and days, not just full years? Yes, by combining several DATEDIF calls with different units — for example, =DATEDIF(A2,TODAY(),"Y")&" years, "&DATEDIF(A2,TODAY(),"YM")&" months" builds a combined "X years, Y months" result from two separate DATEDIF calculations.