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.
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.
Try the date calculatorAdd or subtract days, months, and years, or find the interval between two dates.Working days
Swipe sideways to compare columns.
| 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 |
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.
Months, which are not a fixed number of days
Swipe sideways to compare columns.
| 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.
Swipe sideways to compare columns.
| 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 |
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.
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.
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.
Written by
Do The Calculation Team
Do The Calculation
Do The Calculation is built by a small team of data analysts and spreadsheet developers. Where a guide depends on a published formula, standard, or government rule, the calculator it links to names that source directly so you can check the number yourself.
About the team