Excel Date Functions Cheat Sheet: TODAY, DATEDIF, EOMONTH and More
Excel stores every date as a serial number — January 1, 1900 is day 1, and June 11, 2026 is day 46,184 — which is why you can subtract one date from another and get a sensible answer. Once you internalize that, the entire date function library stops feeling like magic and starts feeling like arithmetic. This cheat sheet covers the functions I actually use week to week, with the exact syntax, what each one returns, and a working example you can paste into a cell right now.
The quick reference table
Bookmark this. Every function below gets a fuller treatment further down the page.
| Function | Syntax | Returns |
|---|---|---|
| TODAY | =TODAY() | Current date (updates on recalculation) |
| NOW | =NOW() | Current date and time |
| DATE | =DATE(year, month, day) | A proper date built from three numbers |
| DATEDIF | =DATEDIF(start, end, "unit") | Whole years, months, or days between dates |
| EOMONTH | =EOMONTH(start, months) | Last day of a month, N months away |
| EDATE | =EDATE(start, months) | Same day of month, N months away |
| NETWORKDAYS | =NETWORKDAYS(start, end, [holidays]) | Working days between two dates |
| WORKDAY | =WORKDAY(start, days, [holidays]) | Date that is N working days away |
| YEAR / MONTH / DAY | =YEAR(date) etc. | The year, month number, or day number |
| WEEKDAY | =WEEKDAY(date, [type]) | Day-of-week number (1–7) |
| YEARFRAC | =YEARFRAC(start, end, [basis]) | Fraction of a year between two dates |
| DATEVALUE | =DATEVALUE("text") | Converts a date stored as text into a real date |
The everyday core: TODAY, NOW, and DATE
TODAY and NOW
=TODAY() returns the current date and takes no arguments — the parentheses stay empty. It’s volatile, meaning it recalculates every time the workbook recalculates, so a report you open next Tuesday shows next Tuesday’s date. The TODAY function is the backbone of nearly every calculation on this page: ages, countdowns, overdue flags all start with it.
=NOW() does the same thing but includes the time, which matters for timestamps and hourly tracking.
One hard-won tip: if you need a date that doesn’t change — say, “date this invoice was created” — don’t use TODAY(). Press Ctrl + ; to stamp a static date, or Ctrl + Shift + ; for a static time.
DATE
=DATE(2026, 6, 11) returns June 11, 2026. It looks trivial until you need to build dates from pieces — pulling year, month, and day out of separate columns, or doing math like “first day of next year”:
=DATE(YEAR(TODAY())+1, 1, 1)DATE also self-corrects out-of-range values: =DATE(2026, 14, 1) rolls over to February 1, 2027. That quirk is genuinely useful for month arithmetic.
DATEDIF: the hidden workhorse
DATEDIF doesn’t appear in Excel’s function autocomplete and has no tooltip — it’s a holdover kept for Lotus 1-2-3 compatibility — but it works in every modern version and it’s the cleanest way to get whole units between two dates.
=DATEDIF(start_date, end_date, "unit")| Unit | Returns |
|---|---|
"Y" | Complete years |
"M" | Complete months |
"D" | Days (same as subtraction) |
"YM" | Months, ignoring years |
"MD" | Days, ignoring months and years |
"YD" | Days, ignoring years |
Example — age in years: with a birth date in A2:
=DATEDIF(A2, TODAY(), "Y")Example — readable tenure string:
=DATEDIF(A2,TODAY(),"Y")&" years, "&DATEDIF(A2,TODAY(),"YM")&" months"Two warnings. First, the end date must be on or after the start date or you get #NUM!. Second, the "MD" unit has known edge-case bugs around month-end dates — avoid it in anything that matters. There’s a deeper walkthrough in our DATEDIF function guide, and a step-by-step on calculating age from a date of birth if that’s your actual goal.
EOMONTH and EDATE: month math done right
EOMONTH
=EOMONTH(start_date, months)Returns the last day of the month, offset by months. This is the function for billing cycles, accruals, and anything that closes at month-end:
=EOMONTH(TODAY(), 0)→ last day of the current month=EOMONTH(TODAY(), -1)→ last day of last month=EOMONTH(TODAY(), -1) + 1→ first day of the current month (the classic trick)
A real one I use constantly — invoice due “30 days end of month,” with the invoice date in A2:
=EOMONTH(A2, 1)The result comes back as a serial number, so format the cell as a date. Full details in the EOMONTH function guide.
EDATE
=EDATE(start_date, months)Same idea, but it keeps the day of the month instead of jumping to the end: =EDATE(DATE(2026,6,11), 3) returns September 11, 2026. Perfect for subscription renewals and loan schedules. If the target month is shorter (January 31 + 1 month), EDATE sensibly returns the last day available (February 28 or 29). Full details in the EDATE function guide.
Working days: NETWORKDAYS and WORKDAY
These two are mirror images, and people mix them up constantly:
- NETWORKDAYS counts the working days between two dates you already have.
- WORKDAY finds the date that lands N working days from a start point.
NETWORKDAYS
=NETWORKDAYS(start_date, end_date, [holidays])Counts Monday–Friday between the dates, inclusive of both endpoints, minus any dates in the optional holidays range. Project length with a holiday list in H2:H12:
=NETWORKDAYS(A2, B2, $H$2:$H$12)More examples in the NETWORKDAYS guide. If your weekend isn’t Saturday–Sunday — common in the Middle East, or for compressed schedules — use NETWORKDAYS.INTL, which lets you define the weekend with a code or a 7-character mask like "0000011".
WORKDAY
=WORKDAY(start_date, days, [holidays])“Ship within 10 business days” with the order date in A2:
=WORKDAY(A2, 10, $H$2:$H$12)Note that WORKDAY does not count the start date itself as day 1. The WORKDAY guide covers negative offsets (counting backward) too.
Pulling dates apart: YEAR, MONTH, DAY, WEEKDAY
These extractors each take one date and return a number:
=YEAR(A2)→ 2026=MONTH(A2)→ 6=DAY(A2)→ 11=WEEKDAY(A2)→ 5 (Thursday, with the default Sunday=1 numbering)
They shine in combination. Flag weekend dates for conditional formatting:
=WEEKDAY(A2, 2) >= 6The 2 return type numbers Monday=1 through Sunday=7, which makes the weekend test readable. Group transactions by year with =YEAR(A2) in a helper column and you’ve saved yourself a pivot table headache.
The calculations everyone actually needs
Age (or tenure) in years
=DATEDIF(birth_date, TODAY(), "Y")For fractional years — useful in HR and finance — use the YEARFRAC function instead: =YEARFRAC(birth_date, TODAY()) returns something like 38.74, which you can then ROUND or floor as needed.
Countdown to a deadline
=DATE(2026,12,25) - TODAY()Plain subtraction returns the number of days remaining. Wrap it for display:
="Days until launch: " & (B1 - TODAY())If the result shows as a date like “1/19/1900” instead of a number, the cell inherited date formatting — switch it back to General. For business days remaining, swap in =NETWORKDAYS(TODAY(), B1).
End of month / first of month
=EOMONTH(A2, 0) → month-end of A2's month
=EOMONTH(A2, -1) + 1 → first day of A2's monthOverdue flag
=IF(due_date < TODAY(), "OVERDUE", "")Pair it with conditional formatting on the date column and overdue rows light up on their own.
When your “dates” are actually text
The most common date problem isn’t a formula at all — it’s imported data where dates are text strings. The tell: they’re left-aligned, and =ISNUMBER(A2) returns FALSE. Fix them with =DATEVALUE(A2), or for stubborn formats, see how to convert text to dates. Once they’re real serial numbers, every function above works. And remember that what a date looks like is just formatting — if you need ISO format, long dates, or custom codes like ddd, mmm d, that’s a trip to changing the date format, not a formula change.
FAQ
Why does my date formula return a number like 46184?
That’s the raw serial number — the formula worked, but the cell is formatted as General. Select the cell, press Ctrl + 1, and choose a Date format. The reverse problem (a subtraction showing “1/19/1900”) means a count of days got formatted as a date; set it back to General.
Why doesn’t DATEDIF show up when I type it?
It’s an undocumented compatibility function, so Excel hides it from autocomplete and the formula wizard. Type it manually with all three arguments and it calculates fine in every current version of Excel, including Excel for the web.
What’s the real difference between EDATE and EOMONTH?
Both shift a date by whole months. EDATE preserves the day of the month (March 15 + 2 months = May 15), while EOMONTH always snaps to the last day of the resulting month (March 15 + 2 months = May 31). Use EDATE for anniversaries and renewals, EOMONTH for anything tied to month-end close.
How do I make NETWORKDAYS respect my company’s holidays?
List the holiday dates in a spare range — one real date per cell — and pass that range as the third argument: =NETWORKDAYS(A2, B2, $H$2:$H$12). Lock the reference with dollar signs so it survives copying down. WORKDAY accepts the same holiday range as its third argument.
