Excel Formulas for Dates: Five Functions and the Results They Return
Most lists of Excel formulas stop at SUM, IF and VLOOKUP. Dates are where a spreadsheet earns its keep, because the questions are concrete: when does this subscription renew, how many working days are left before the deadline, how old is this person today. Five functions cover most of them.
Below is each one with its syntax and the result we got when we typed the example into LibreOffice Calc 26.2.6.3 on October 25, 2026, and checked it against a count in Python. We did not run them in Excel itself. The definitions come from the function pages on Microsoft Support, which warn that dates entered as text can cause problems, so use real dates or the DATE function.
What date is three months after January 31?
EDATE moves a date forward or back by whole months. The syntax is EDATE(start_date, months).
With A2 holding January 31, 2026, =EDATE(A2, 3) returns April 30, 2026. April has no 31st, so the result stops at the last day of the month. With A2 holding October 15, 2026, the same formula returns January 15, 2027. A negative number of months goes back.
That makes it the natural choice for renewal dates and installment schedules, where “the same day next month” is the question.
What is the last day of this month?
EOMONTH returns the last day of a month: EOMONTH(start_date, months). A 0 means the same month and a 1 would mean the next one.
With A2 holding October 15, 2026, =EOMONTH(A2, 0) returns October 31, 2026. With February 10, 2028 it returns February 29, 2028, so you never have to remember which years are leap years.
How many working days between two dates?
NETWORKDAYS counts working days and leaves out Saturdays, Sundays and any holidays you list: NETWORKDAYS(start_date, end_date, [holidays]).
With A2 holding December 21, 2026 and B2 holding January 4, 2027, =NETWORKDAYS(A2, B2, H2:H12) returned 9 after we typed December 25 and January 1 into H2 and H3. With H2:H12 empty it returned 11. The two holidays took away 2 days.
What date is ten working days from now?
WORKDAY goes the other direction. It returns the date that is a number of working days away: WORKDAY(start_date, days, [holidays]).
Get Wakhid Wicaksono’s stories in your inbox
Join Medium for free to get updates from this writer.
With A2 holding Monday, October 26, 2026, =WORKDAY(A2, 10) returned Monday, November 9, 2026. Starting on Friday, October 30, 2026, it returned Friday, November 13, 2026. Both land on the same weekday as the start, because ten working days is exactly two weeks. A negative number of days goes back.
How old is someone, in complete years?
DATEDIF measures the gap between two dates in the unit you give in quotes: DATEDIF(start_date, end_date, unit).
With A2 holding May 14, 1990, =DATEDIF(A2, TODAY(), “Y”) returned 36 on October 25, 2026. The unit “Y” counts complete years, “M” counts complete months and “D” counts days. To see where it turns over, use a fixed end date instead of TODAY(): May 13, 2027 gave 36 and May 14, 2027 gave 37, so the age rolls over on the birthday and not before.
Microsoft advises against the “MD” unit because of known limitations. I’d stay with Y, M and D.
Which one to reach for
The five split into two pairs and a loner, and the split makes the choice easier.
- Moving by months: EDATE gives the same day in another month, and EOMONTH gives the last day of one.
- Working days: NETWORKDAYS counts the days between two dates you already have, while WORKDAY finds the date when you know how many days you have. Both accept a list of holidays as an optional last argument.
- Elapsed time: DATEDIF reports the gap in years, months or days.
One check applies to all of them. If a result looks like a five-digit number instead of a date, the cell is formatted as General, and a date format fixes the display without changing the value. If a formula returns an error, look at the input first: a date typed as text is the usual cause, which is why Microsoft recommends real dates or the DATE function.
A note on versions
None of these five carries a version tag on the cheat sheet. The tags there mark newer functions, such as XLOOKUP (Excel 2021 and later) or TEXTSPLIT (Excel 2024 and later), and these five are not among them.
The rest of the sheet
These five make up one card of eight on the free Excel formulas cheat sheet, a one-page landscape PDF with 40 functions grouped by task. Every row shows the syntax and a short example, in US Letter and A4, with no email or account needed.
Type your own dates into the examples above and compare them with the results here before you rely on them in a real file.
