# Date Arithmetic in Excel: NETWORKDAYS, EDATE, EOMONTH, and the DATEDIF Trap Adding 30 to a date lands on a Sunday. Adding one month to 31 January has no obvious answer. Here are the functions that handle both properly, plus the undocumented one that returns wrong answers and the two you should never leave in a saved file. --- - **Canonical URL:** https://dothecalculation.com/blog/templates/excel-date-and-time-functions - **Category:** Templates - **Author:** Do The Calculation Team - **Published:** 2026-08-03 - **Reading time:** 10 min read - **Publisher:** Do The Calculation (https://dothecalculation.com) - **Methodology:** https://dothecalculation.com/methodology --- ## Dates Are Numbers, Which Explains Most of the Confusion Excel stores a date as the number of days since a fixed origin, and a time as the fraction of a day. That is why subtracting two dates gives a plain number, why adding 1 moves forward one day, and why 6 hours is 0.25. Once that is clear, the function library divides into three groups: those that handle working days, those that handle months, and those that handle durations. Each group has one member worth knowing well and one worth avoiding. Tool: [Try the date calculator](https://dothecalculation.com/calculators/date-calculator) — Add or subtract days, months, and years, or find the interval between two dates. ## Working days **The four working-day functions** | Function | Returns | Use for | | --- | --- | --- | | NETWORKDAYS(start, end, [holidays]) | A count of working days | Elapsed business days between two dates | | NETWORKDAYS.INTL(start, end, [weekend], [holidays]) | The same, with a custom weekend | Non Saturday–Sunday weekends | | WORKDAY(start, days, [holidays]) | A date | A deadline n working days out | | WORKDAY.INTL(start, days, [weekend], [holidays]) | The same, with a custom weekend | The same, in other regions | > **NETWORKDAYS counts both endpoints** — Monday to Friday of the same week returns 5, not 4. That is a count of working days, not an elapsed duration. If you want elapsed days, subtract 1. Getting this wrong shifts every service-level report by a day. The holidays argument is a range of dates, and it is worth maintaining on its own sheet. Without it, every deadline calculation in the workbook is wrong across a public holiday, and it fails silently. **A custom weekend** ``` =NETWORKDAYS.INTL(start, end, "0000011", Holidays) ``` - The string has seven characters, Monday first, where 1 means a non-working day. - "0000011" is the standard Saturday and Sunday weekend. - "0000110" makes Friday and Saturday the weekend; "0000001" gives a six-day week with only Sunday off. ## Months, which are not a fixed number of days **EDATE and EOMONTH** ``` =EDATE(date, months) · =EOMONTH(date, months) ``` - EDATE returns the same day number, months later. EOMONTH returns the last day of that month. - Both accept negative months to go backwards. - EOMONTH(date, 0) is the end of the current month; EOMONTH(date, −1)+1 is the first day of the current month. **What each returns from 31 January** | Formula | Result | Note | | --- | --- | --- | | =EDATE("31-Jan", 1) | 28 or 29 February | Clamped, because 31 February does not exist | | =EDATE("31-Jan", 3) | 30 April | Clamped again | | =EOMONTH("31-Jan", 1) | 28 or 29 February | Same answer, different reason | | =EOMONTH("15-Jan", 1) | 28 or 29 February | End of month regardless of the input day | | ="31-Jan"+30 | 2 March | Rarely what anyone means by "a month" | The clamping is worth knowing because it is not reversible. EDATE from 31 January forward one month and back one month returns 28 February, not 31 January. Any schedule built by repeatedly adding one month drifts to the 28th and stays there. Build monthly schedules from a fixed anchor: =EDATE(StartDate, ROW()-1). ## Durations, and the function to avoid DATEDIF computes the interval between two dates in years, months, or days. It is a compatibility function from Lotus 1-2-3, it does not appear in the function list, it offers no argument tooltips, and two of its six unit codes return wrong answers. **DATEDIF unit codes** | Code | Meaning | Safe? | | --- | --- | --- | | "Y" | Complete years | Yes | | "M" | Complete months | Yes | | "D" | Days | Yes, but end minus start is clearer | | "YM" | Months, ignoring years | Yes | | "YD" | Days, ignoring years | Unreliable across leap years | | "MD" | Days, ignoring months and years | Documented by Microsoft as capable of returning a negative number | > **Where DATEDIF is still the right answer** — Age in whole years: =DATEDIF(DOB, TODAY(), "Y"). Nothing else does this correctly in one function, because a year is not a fixed number of days and (TODAY()-DOB)/365.25 rounds wrongly near a birthday. Use it for that, avoid "MD" and "YD", and never build a payroll or billing calculation on it. **Age as years and months, without "MD"** ``` =DATEDIF(DOB, TODAY(), "Y") & " years, " & DATEDIF(DOB, TODAY(), "YM") & " months" ``` - "Y" and "YM" are both reliable; it is only "MD" and "YD" that misbehave. - YEARFRAC(DOB, TODAY()) gives a decimal age, useful for calculations rather than display. - YEARFRAC takes a day-count basis argument that changes the answer, so state which you used. ## Time, and why totals stop at 24 hours A time is a fraction of a day, so 8 hours 30 minutes is 0.354167. Summing a week of timesheet entries gives a number above 1, and the default time format displays only the fractional part. Forty-two hours shows as 18:00. **The two fixes** ``` Format the cell as [h]:mm · or convert to decimal hours with =SUM(range)*24 ``` - The square brackets tell Excel not to roll over at 24 hours. This is a number format, not a formula. - Multiplying by 24 gives decimal hours, which is what you need for a pay calculation. - 42:00 in [h]:mm and 42.00 as decimal hours are the same quantity in two presentations. A shift crossing midnight is the other classic. End time minus start time goes negative when the end is 02:00 and the start is 22:00. Use =MOD(End − Start, 1), which returns 0.166667, or four hours, without any conditional logic. ## The two functions to keep out of saved files TODAY and NOW are volatile: they recalculate every time the workbook opens or anything changes. That is right for a dashboard and wrong for a record. - An invoice date entered with =TODAY() will show today rather than the date the invoice was issued, forever. - Ctrl+semicolon types a static date; Ctrl+shift+semicolon types a static time. - To keep a formula result, press F9 while editing to convert it in place, or copy and paste as values. - Volatile functions also force recalculation of everything that depends on them, which is a real performance cost in a large workbook. ## Three quirks worth knowing about - Serial number 60 is 29 February 1900, a day that did not exist. Excel keeps the error deliberately for compatibility with Lotus 1-2-3. It affects only dates before 1 March 1900, so in practice it is trivia rather than a hazard. - Workbooks created on older Mac versions may use a 1904 date system, which shifts every serial number by 1,462 days. Pasting dates between the two systems shifts them by four years and a day. - Text that looks like a date is not a date. Imported bank exports are the usual culprit. Right-aligned means Excel parsed it; left-aligned means it is text and every date function will fail or, worse, return something plausible. Tool: [Try the hours between dates calculator](https://dothecalculation.com/calculators/hours-between-dates-calculator) — Compute elapsed hours across dates, including shifts that cross midnight. ## What these functions do not handle - Time zones. Excel has no concept of them. A timestamp is a number, and converting between zones means adding a fraction of a day yourself. - Daylight saving. A day with a clock change is 23 or 25 hours long, and every duration calculation across it is out by an hour. - Holidays, unless you supply them. There is no built-in calendar, and the holiday list needs maintaining every year. - Business hours within a day. NETWORKDAYS counts whole days, so a service-level target of four business hours needs its own logic. - Fiscal calendars. A year starting in April, or a 4-4-5 retail calendar, needs a lookup table rather than a date function. - Non-Gregorian calendars, beyond limited support that varies by locale and version. **Why does NETWORKDAYS give one more day than I expected?** Because it counts both the start and the end date. Monday to Friday returns 5. That is the number of working days in the range, not the elapsed time between them. Subtract 1 if you want elapsed working days. **How do I add one month to a date?** Use =EDATE(date, 1). Adding 30 or 31 days is not the same thing and drifts across the year. EDATE clamps to the last day of the target month when the day number does not exist, so 31 January plus one month is the end of February. **Why does my timesheet total show 18:00 instead of 42:00?** The cell is formatted as a time, which rolls over at 24 hours. Change the number format to [h]:mm, with the square brackets, or multiply the sum by 24 to get decimal hours. The underlying value is correct in both cases. **Is DATEDIF safe to use?** For "Y", "M", and "YM", yes. The "MD" code can return a negative number and "YD" is unreliable across leap years. It is undocumented in the function list and kept only for compatibility, so use it for age in whole years and prefer explicit arithmetic elsewhere. **How do I handle a shift that crosses midnight?** Use =MOD(End − Start, 1) rather than a plain subtraction. MOD wraps the negative result back into a positive fraction of a day, so 22:00 to 02:00 returns four hours with no IF statement. **Why will my imported dates not sort or group?** They are text, not dates. Check the alignment: text left-aligns and dates right-align by default. Fix with Data, Text to Columns, then Finish, which reparses the column, or use Power Query where you can set the source locale explicitly. --- _Source: [Do The Calculation](https://dothecalculation.com/blog/templates/excel-date-and-time-functions). Quote freely with attribution and a link to this page._