Date Calculations in Excel — Complete Guide (2026)

Date Calculations in Excel: The 2026 Working Guide

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.

DATEDIF — The Secret Function Microsoft Won't Document

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.

Simple Date Subtraction and Business Days

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.

EDATE and EOMONTH — Month Arithmetic Done Right

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.

Real-World Date Calculations You'll Actually Need

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.

Edge Cases, Pitfalls, and Final Tips

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.