Dates in Excel aren't really dates. They're serial numbers wearing a costume. January 1, 1900 is 1. January 2, 1900 is 2. Today's date is somewhere around 46,300. That's it — that's the whole trick behind every date calculation you'll ever write. Once you accept that dates are just numbers, the formulas stop feeling mysterious and start feeling obvious.
Subtraction works. =B1-A1 returns the number of days between two dates because you're literally subtracting two integers. DATEDIF goes further with months and years. NETWORKDAYS skips weekends. EDATE jumps forward or backward by whole months without breaking on the 31st. EOMONTH lands on the last day of any month you point it at. Each function exists because someone got tired of writing the same workaround a hundred times.
This guide covers the functions you'll actually use — DATEDIF, NETWORKDAYS, NETWORKDAYS.INTL, WORKDAY, EDATE, EOMONTH, TODAY, NOW — plus the common patterns: age from a birthdate, project duration, days until a deadline, contract expiration, fiscal quarters. We'll also handle the things that bite people: negative results, international date formats, text that looks like a date but isn't, and the quirks of DATEDIF (which Microsoft still hasn't documented in 30 years).
Short answer for the impatient. Days between two dates: =B1-A1. Years between two dates: =DATEDIF(A1,B1,"y"). Business days between: =NETWORKDAYS(A1,B1). Add 6 months: =EDATE(A1,6). Last day of next month: =EOMONTH(TODAY(),1). Days until a deadline: =A1-TODAY(). That's 80% of what people ask for, all in one paragraph.
One thing worth knowing before you start. If your dates won't subtract, they're text. Excel will show "01/15/2026" in a cell whether it's a real date or a string that looks like one. Click the cell. If it's right-aligned, it's a date. Left-aligned, it's text. The DATE function — =DATE(2026,1,15) — builds a real date from three numbers and bypasses the parsing problem entirely. Use it whenever you're not sure.
The pattern across this guide is the same: pick the right function, feed it real dates (not text), and let Excel do the arithmetic. Most date calculation problems aren't formula problems. They're data problems hiding in formula clothing. If you can spot the difference, you'll fix things in seconds instead of hours.
Returns difference between two dates as years, months, or days. Undocumented but works in every Excel version. Syntax: =DATEDIF(start, end, "y"/"m"/"d"/"ym"/"yd"/"md").
Counts business days between dates, automatically skipping Saturdays and Sundays. Optional holiday list as third argument. =NETWORKDAYS(start, end, [holidays]).
Returns the date that is N business days after a start date. Excludes weekends and optional holidays. =WORKDAY(start, days, [holidays]).
Adds or subtracts whole months from a date without breaking on month-end. =EDATE(date, months) — feed positive or negative integers.
Returns the last day of the month, N months from a given date. Perfect for billing cycles and fiscal periods. =EOMONTH(date, months).
TODAY() returns the current date (no time). NOW() returns date and time. Both recalculate every time the workbook opens — volatile functions.
DATEDIF is the strangest function in Excel. It works in every version from Excel 95 to Microsoft 365. It's not in the function wizard. It's not in the official documentation. Type it in the formula bar and Excel won't even autocomplete the name. But it works, and it's the cleanest way to get years, months, or days between two dates without writing nested IF statements.
The syntax: =DATEDIF(start_date, end_date, unit). The six unit codes are "y" for complete years, "m" for complete months, "d" for total days, "ym" for months ignoring years, "yd" for days ignoring years, and "md" for days ignoring months and years. The last three are the secret weapons for building human-readable durations like "3 years, 4 months, 12 days."
Age from a birthdate is the textbook example. =DATEDIF(B2, TODAY(), "y") returns the person's age in complete years. Add =DATEDIF(B2, TODAY(), "ym") and =DATEDIF(B2, TODAY(), "md") to get the months and days remaining. Concatenate them with text and you get "34 years, 2 months, 7 days" in a single cell — a calculation that would take 20 lines of formula logic without DATEDIF.
DATEDIF has one major bug. The "md" unit returns negative numbers in rare edge cases when the day-of-month in the end date is smaller than the start date. Microsoft acknowledged it in a 2007 KB article and never fixed it. If you're calculating ages or anniversaries that span month-end transitions, validate the "md" result with an IF wrapper. For most use cases — counting whole years or months — it works perfectly.
One alternative pattern uses the YEAR, MONTH, and DAY functions together. =YEAR(B1)-YEAR(A1) gives raw year difference but doesn't account for whether the birthday has happened yet this year. Combine it with an IF check on the month and day, and you can replicate DATEDIF's behavior. Most people skip this and just use DATEDIF because it's shorter. Anchor texts like datedif excel get the highest search volume in this category, which tells you how many people are looking for exactly this function.
DATEDIF returns errors when the start date is after the end date. Wrap it in an IF that swaps the arguments if needed. =IF(A1>B1, DATEDIF(B1,A1,"d"), DATEDIF(A1,B1,"d")) — boring, but bulletproof. The function also returns #NUM! if you pass an invalid unit string, so spell-check those quotes.
The straightforward units. "y" returns complete years between dates — January 5, 2020 to January 4, 2026 returns 5, not 6, because the sixth year hasn't completed. "m" returns complete months. "d" returns total days, identical to a plain B1-A1 subtraction.
Use "y" for ages, anniversaries, tenure calculations. Use "m" for contract duration in months. Use "d" when you want one cell to show the day count without doing math elsewhere.
The "ym" unit returns months between dates while ignoring the year. January 15, 2020 to April 10, 2026 returns 2, not 74, because 74 months counts the years too. Use "ym" as the middle component of a "X years, Y months" display.
Combined formula: =DATEDIF(A1,B1,"y")&" yrs, "&DATEDIF(A1,B1,"ym")&" mos" produces something like "4 yrs, 3 mos" — perfect for HR tenure reports, customer lifetime, project duration summaries.
"yd" returns days between dates ignoring years. "md" returns days ignoring both months and years. Both feel weird until you build a tenure string: 4 years, 3 months, 12 days — the 12 comes from "md".
Warning: "md" can return wrong values when the end date's day-of-month is smaller than the start's. Microsoft confirmed this bug in 2007 and never fixed it. For mission-critical work, validate with: =IF(DATEDIF(A1,B1,"md")<0, 0, DATEDIF(A1,B1,"md")).
Age in years: =DATEDIF(birthdate, TODAY(), "y")
Service months at a company: =DATEDIF(hire_date, TODAY(), "m")
Days until contract ends: =DATEDIF(TODAY(), end_date, "d")
Full age string: =DATEDIF(A1,TODAY(),"y")&"y "&DATEDIF(A1,TODAY(),"ym")&"m "&DATEDIF(A1,TODAY(),"md")&"d" → "34y 2m 17d"
Subtraction is the most common date operation, and it's the easiest. =B1-A1 where B1 is the later date returns the number of days between them. Excel does this because dates are stored as serial numbers — January 15 is 45,672 and January 10 is 45,667, and 45,672 minus 45,667 is 5. Format the result cell as Number (not Date), or it'll show as a date in 1900.
NETWORKDAYS is the function you want when weekends shouldn't count. =NETWORKDAYS(A1, B1) returns the number of Monday-through-Friday days between two dates, inclusive of both endpoints if they fall on weekdays. Add a holiday list as the optional third argument — a range of cells containing public holidays — and those drop out too. Most payroll, project management, and SLA calculations live inside this function.
NETWORKDAYS.INTL takes it further. The third argument is a workweek code that lets you customize which days count as weekends. "1" is the default Mon-Fri. "7" is Sun-Mon weekends (used in some Middle Eastern countries). You can also pass a 7-character string like "0000011" where 1 = weekend day and 0 = workday, ordered Mon-Sun. =NETWORKDAYS.INTL(A1, B1, "0000011", holidays) handles any custom schedule.
WORKDAY does the inverse. Give it a start date and a number of business days, and it returns the date that lands N business days later. =WORKDAY(A1, 10) returns the date 10 business days after A1, skipping weekends. Use this for shipping ETAs, response deadlines, or any "add X business days" calculation. WORKDAY.INTL accepts the same custom workweek codes as NETWORKDAYS.INTL.
The trick most people miss: holidays in NETWORKDAYS must be a range or array of real dates, not text. Build a holidays sheet with one date per row, name the range "USHolidays" or whatever fits, then reference it in every NETWORKDAYS formula. =NETWORKDAYS(start, end, USHolidays). Keep the list updated once a year and every downstream formula updates automatically. The same approach makes excel date functions reusable across the entire workbook.
One common mistake: subtracting dates that came from text imports. If you import data from a CSV and dates show up as text strings (left-aligned in cells), no formula will work on them. Wrap them in DATEVALUE first: =DATEVALUE("1/15/2026") returns the serial number. Or use the DATE function: =DATE(2026, 1, 15) — safest because it doesn't depend on regional format parsing.
If you DATEDIF Jan 5, 2020 to Jan 4, 2026 with the "y" unit, you get 5 — not 6. The sixth year hasn't completed because Jan 4 is one day before the anniversary. This trips up payroll, vesting schedules, and benefits calculations constantly. If you need rounded-up years, use ROUNDUP((B1-A1)/365.25, 0) instead. If you need calendar-year difference regardless of completion, use YEAR(B1)-YEAR(A1). Pick the right one for the business rule, not just the formula that compiles.
Adding months to a date sounds trivial until you hit January 31. What's one month after that — February 31? February 28? February 29 in a leap year? EDATE solves this. =EDATE(A1, 1) returns the same day-of-month in the next month, or the last day if that day doesn't exist. Jan 31 + 1 month becomes Feb 28 (or 29 in 2024). Mar 31 + 1 month becomes Apr 30. Done correctly, every time, no leap-year branching.
EDATE takes negative integers too. =EDATE(A1, -6) subtracts six months. =EDATE(TODAY(), 12) gets the same day one year from today (also handles Feb 29 → Feb 28 in non-leap target years). Use it for renewal dates, contract anniversaries, and any "add N months" business rule where you don't want surprises on the 31st.
EOMONTH is even more useful in finance. =EOMONTH(A1, 0) returns the last day of the month A1 falls in. =EOMONTH(A1, 1) returns the last day of next month. =EOMONTH(A1, -1) returns the last day of last month. Combine with TODAY(): =EOMONTH(TODAY(), 0) is always the last day of this month, perfect for billing cycles, fiscal close, and reporting period end dates.
The pattern these two functions enable: first day of next month = =EOMONTH(A1, 0)+1. First day of any month N months out = =EOMONTH(A1, N-1)+1. Days remaining in current month = =EOMONTH(A1, 0)-A1. None of these require IF logic or manual leap-year handling. EOMONTH does the boundary work, EDATE does the month-shifting, and your formulas stay readable.
For quarters, build on top of these. Fiscal quarter from a date: ="Q"&CEILING(MONTH(A1)/3, 1). Quarter-end date: =EOMONTH(DATE(YEAR(A1), CEILING(MONTH(A1)/3,1)*3, 1), 0). Year-end: =DATE(YEAR(A1), 12, 31). Each one chains the simple building blocks rather than reinventing the math. The excel date formulas in this section work for any fiscal calendar offset — just shift the month numbers.
One quirk: EDATE and EOMONTH return serial numbers, not formatted dates. Format the result cell as a date or you'll see something like "45720" instead of "3/15/2026". This catches new users every time. Right-click the cell, Format Cells, Date, pick a format. The math was always right — Excel just needed permission to dress it up.
Age from a birthdate. Put the birthdate in A1, then =DATEDIF(A1, TODAY(), "y") returns whole years. For "34 years, 5 months" precision: =DATEDIF(A1, TODAY(), "y")&" yrs, "&DATEDIF(A1, TODAY(), "ym")&" mos". Updates automatically every time the workbook opens because TODAY() is volatile. Great for HR reports, customer profiles, eligibility checks.
Days until a deadline. =A1-TODAY() where A1 is the deadline. Returns negative if the deadline has passed — wrap in IF for cleaner display: =IF(A1>=TODAY(), A1-TODAY()&" days left", "OVERDUE by "&TODAY()-A1&" days"). Use it on project trackers, contract expirations, license renewals. The volatile TODAY() recalculation means the workbook always shows current status without manual refresh.
Contract duration in months. =DATEDIF(start_date, end_date, "m") returns whole completed months. If you want fractional months — useful for pro-rated billing — use (end_date - start_date) / 30.44, where 30.44 is the average days per month. Not exact, but close enough for invoice calculations. For exact calendar-month billing, EOMONTH-based logic is more reliable than dividing by an average.
Business days until a project deadline. =NETWORKDAYS(TODAY(), A1, holidays). If the holidays range covers the year, this gives a realistic working-day countdown — much more useful than calendar days when planning sprints, vendor responses, or shipping windows. Combine with the how to subtract dates in excel patterns from earlier sections to build a complete countdown dashboard.
Project end date from a start date plus duration. If a 30-business-day project starts on A1: =WORKDAY(A1, 30, holidays). Returns the actual calendar date when the project will finish if it runs straight through, excluding weekends and holidays. Useful for capacity planning, client communication, and dependency chains in Gantt charts. Adjust the 30 to whatever your duration is.
License or subscription expiration alerts. =IF(EDATE(A1, 12)<=TODAY()+30, "RENEW SOON", "OK") where A1 is the last renewal date. Triggers a flag 30 days before the next annual renewal. Apply conditional formatting to highlight in red. Works for software licenses, professional certifications, vehicle registrations, anything on a yearly cycle. Swap 12 to 6 for semi-annual, 3 for quarterly.
Negative results. When B1-A1 returns a negative number, the dates are in the wrong order. Wrap with ABS for absolute distance: =ABS(B1-A1). Or use IF to swap: =IF(A1>B1, A1-B1, B1-A1). Most professional templates assume "start before end" by convention and just document it — but defensive formulas don't hurt when you'll share the file.
The 1900 leap year bug. Excel thinks 1900 was a leap year. It wasn't — century years aren't leap years unless divisible by 400. This off-by-one only matters if you're doing date math involving dates before March 1, 1900, which almost nobody does. Lotus 1-2-3 had the bug and Microsoft preserved it for compatibility. Live with it.
Mac Excel and the 1904 date system. Old Mac versions of Excel used Jan 1, 1904 as serial 1 instead of Jan 1, 1900. If you open a Mac workbook on Windows or vice versa, dates can shift by ~1,462 days. Check File → Options → Advanced → "Use 1904 date system" and make sure both systems match. This bites consultants who get files from clients running mixed environments.
Text that looks like a date. Excel sometimes refuses to recognize a date because of trailing spaces, hidden characters, or wrong delimiters. TRIM and CLEAN the cell first: =DATEVALUE(TRIM(CLEAN(A1))). If still failing, the date format probably doesn't match your regional settings — use DATE(YEAR, MONTH, DAY) with explicit components extracted via LEFT/MID/RIGHT. Tedious but reliable. Patterns like excel date difference calculations only work when inputs are real dates.
Volatile function performance. TODAY() and NOW() recalculate every time anything in the workbook changes. In a 50,000-row spreadsheet with 100 TODAY() formulas, that's 5 million recalcs on every keystroke. If the file feels sluggish, replace TODAY() with a single cell that holds today's date and reference it everywhere: enter =TODAY() in cell Z1, then use $Z$1 in every formula. Saves enormous amounts of recalc time.
Date axis charts. If a chart's X-axis treats dates as text instead of dates, gaps between unequal date intervals collapse and the chart lies. Right-click the X-axis, Format Axis, set Axis Type to "Date axis" not "Text axis". Now Excel uses actual date spacing — March 1 to March 10 takes 9 units of space, March 10 to April 1 takes 22 units. The chart finally reflects reality.