Excel Date and Time Formulas: Serial Numbers, EOMONTH, NETWORKDAYS and DATEDIF

Dates are the one data type in Excel that looks like text, behaves like a number, and breaks like neither. A column of dates will sort itself into nonsense, refuse to be summed, silently drop half the rows out of a SUMIFS, and display ##### for reasons that have nothing to do with an error.

Almost all of that comes from one misunderstanding, and fixing it fixes everything downstream: a date in Excel is not a date. It is a number wearing a costume.

Tip: Select any date cell and press Ctrl + Shift + ~ (the General format shortcut). If a number appears, it is a real date. If nothing changes, it is text pretending to be one — and section 9 is the section you need.

1) The Serial Number Model

Excel stores a date as the count of days since a fixed origin. In the Windows date system, day 1 is 1 January 1900, so:

Times use the fractional part of the same number. One day is 1, so one hour is 1/24 and one minute is 1/1440:

A cell showing 09/01/2026 18:00 therefore holds 46031.75. The date part is the integer, the time part is the decimal, and the format is the only thing that decides which parts you see.

Four consequences worth internalising:

1. You can do arithmetic on dates. = date + 30 is thirty days later. = end - start is a number of days. No function required.
2. INT strips the time. =INT(A2) turns a timestamp into a clean date — the standard fix for "my dates won't group in a PivotTable".
3. MOD strips the date. =MOD(A2,1) leaves the time of day alone.
4. Anything before 1900 isn't a date at all. Excel cannot store 1875 as a serial number. Historical data has to live as text and be handled by other means.

The famous bug. Excel believes 29 February 1900 existed. It did not — 1900 was not a leap year. The error was inherited deliberately from Lotus 1-2-3 for file compatibility and has never been fixed, because fixing it would shift every date in every workbook ever saved. In practice it means serial 60 is a day that does not exist, and any date before 1 March 1900 is one day out. Nobody's invoices are from February 1900, so this is trivia — until you try to reconcile Excel serials against another system's day count and land exactly one day apart.

The other date system. Older Mac workbooks may use the 1904 system, where day 0 is 1 January 1904. Paste dates from a 1904 workbook into a 1900 workbook and every one of them shifts by 1,462 days — just over four years. The setting is under File → Options → Advanced → When calculating this workbook, and the only sane response to finding a mismatch is to convert one file rather than living with the offset.

2) Getting Dates In Without Getting Them Wrong

🎯 Scenario: You need today's date, a hard-coded date that means the same thing to a colleague in another country, and a date assembled from three separate columns.

TODAY() and NOW() take no arguments:

=TODAY()      the current date, no time
=NOW()        the current date and time

Both are volatile: they recalculate on every change anywhere in the workbook, not just when the day rolls over. That is what you want in a live ageing report and exactly what you do not want in an audit log — a "date received" column built from TODAY() will read as today's date forever.

For a stamp that never moves, use the keyboard instead of a function:

DATE(year, month, day) builds a date from three numbers, and it is the only unambiguous way to write a literal date in a formula:

=DATE(2026,1,9)

Typing "09/01/2026" into a formula asks Excel to guess, and its guess depends on the machine's regional settings — 9 January in London, 1 September in New York. DATE cannot be misread.

DATE also rolls over on purpose, which is the source of two of the best tricks in the language:

=DATE(2026,13,1)     →  1 January 2027   (month 13 rolls into next year)
=DATE(2026,2,31)     →  3 March 2026     (Feb has 28 days in 2026; 3 spill over)
=DATE(2026,3,0)      →  28 February 2026 (day 0 = the day before the 1st)

That last one is the classic "last day of the month" formula: =DATE(YEAR(A2), MONTH(A2)+1, 0). It works, and section 4 has a shorter way.

Pitfall: Two-digit years are interpreted by a fixed cutoff — 00 to 29 become 2000–2029, and 30 to 99 become 1930–1999. Typing 1/1/30 gives you 1930, not 2030. On a form that people fill in by hand, this is worth a Data Validation rule.

Mini exercise: In a blank cell, enter =DATE(2026,1,9) and then press Ctrl + Shift + ~. You should see 46031 — the same number from the table in section 1.

3) Taking Dates Apart

🎯 Scenario: You need the month name for a report heading, the year for a filter, and the day of the week to spot invoices issued at the weekend.

The three component functions are exactly what they look like:

=YEAR(C2)     →  2026
=MONTH(C2)    →  1
=DAY(C2)      →  9

They return numbers, not names. For a name you need TEXT, which applies a display format and returns the result as text:

=TEXT(C2,"mmm")        →  Jan
=TEXT(C2,"mmmm yyyy")  →  January 2026
=TEXT(C2,"ddd")        →  Fri
=TEXT(C2,"dddd")       →  Friday

Pitfall: TEXT gives you a label, not a date. "Jan 2026" sorts before "Feb 2026" alphabetically, which happens to look right, but "Apr 2026" sorts before both and "Dec 2025" sorts before all three. Group by a real date — the first of the month — and format it as mmm yyyy for display. Then the sort is chronological because the underlying value still is.

WEEKDAY returns a number, and its second argument decides which number:

With type 2, a weekend test is readable:

=WEEKDAY(D2,2)>5

Result: TRUE for INV-2041, whose due date of 8 February 2026 falls on a Sunday — a due date nobody was ever going to meet on time.

Snapping to the start of a week uses type 3, because subtracting a zero-based weekday lands exactly on Monday:

=C2-WEEKDAY(C2,3)

Week numbers come in two flavours, and they disagree:

• =WEEKNUM(C2,2) — the simple version. Week 1 is whatever week contains 1 January, weeks start on Monday with the 2.
• =ISOWEEKNUM(C2) — the ISO 8601 version. Week 1 is the week containing the first Thursday of the year, always Monday to Sunday.

If your reporting has to line up with anyone else's — a partner, an ERP, a manufacturing calendar — use ISOWEEKNUM. It is the one everybody else's systems mean by "week 3".

4) Month Arithmetic: EDATE and EOMONTH

🎯 Scenario: Payment terms are "end of the following month". Adding 30 days is wrong in February, wrong in a 31-day month, and wrong every leap year.

Adding a number of days is easy. Adding a number of months is not, because months are not a fixed length. Two functions do it properly.

EDATE(start, months) — the same day of the month, some months later:

=EDATE(C2,1)     one month after the issue date
=EDATE(C2,-3)    three months before
=EDATE(C2,12)    the anniversary

It clamps rather than overflows: =EDATE(DATE(2026,1,31),1) returns 28 February 2026, not 3 March. That is almost always the intended behaviour for contracts and subscriptions.

EOMONTH(start, months) — the last day of the month, some months away:

=EOMONTH(C2,0)     end of this month
=EOMONTH(C2,-1)    end of last month
=EOMONTH(C2,1)     end of next month

EOMONTH knows about 30-day months and leap years so you never have to. And because EOMONTH(date,-1)+1 is the first of the current month, it gives you the other half of the pair:

=EOMONTH(C2,-1)+1     the first day of this month
=EOMONTH(C2,0)+1      the first day of next month

Applied to the invoice ledger, "due at the end of the following month" is one function:

=EOMONTH(C2,1)

Result: INV-2041, issued 9 January, becomes due 28 February 2026 — correctly short, without anybody remembering that 2026 is not a leap year.

Building a report skeleton. Twelve month-ends from a starting date, as a single spilling formula in Microsoft 365:

=EOMONTH(DATE(2026,1,1), SEQUENCE(12,1,0,1))

Fiscal periods fall out of EDATE once you shift the year. For a fiscal year running April to March, labelled by the year it starts in:

=YEAR(EDATE(C2,-3))
=ROUNDUP(MONTH(EDATE(C2,-3))/3, 0)

Result: An invoice issued 13 March 2026 belongs to fiscal year 2025, quarter 4 — which is exactly right, and which no amount of nested IF would have expressed as clearly.

Pitfall: EDATE and EOMONTH return a serial number, and if the destination cell was formatted as General you will see 46081 rather than a date. Nothing is broken; the cell just has not been told what it is holding. Format as Short Date and move on.

5) Differences: Subtraction, DATEDIF and YEARFRAC

🎯 Scenario: How many days did each invoice take to be paid, how old is an unpaid one today, and how long has the client been a customer in years and months?

Days: just subtract. There is no function to learn.

=E2-C2

Result: 27 for INV-2041 — issued on the 9th of January, paid on the 5th of February. =DAYS(E2,C2) gives the same answer with the arguments reversed, and exists mostly so the operation has a name.

Days late, only counting real lateness:

=MAX(0, E2-D2)

Result: 0 for the invoices paid on time, 11 for INV-2043, which was due 8 March and paid on the 19th.

Ageing an unpaid invoice needs one branch, because the payment column is empty:

=IF(E2="", MAX(0, TODAY()-D2), MAX(0, E2-D2))

Result: A single "days overdue" column that reports history for settled invoices and live ageing for open ones. Bucket it with LOOKUP, which takes the largest breakpoint not greater than the value:

=LOOKUP(G2, {0,1,31,61,91}, {"Not due","1-30","31-60","61-90","90+"})

Months and years: DATEDIF. It is the odd one out in Excel — a survivor from Lotus 1-2-3 that Microsoft keeps for compatibility. It has no IntelliSense prompt, no argument tooltip, and if you type =DATEDIF( Excel offers you nothing. It still works, in every version, and nothing else does its job:

=DATEDIF(start, end, "unit")

A tenure or age string is the standard use:

=DATEDIF(C2,TODAY(),"Y") & "y " & DATEDIF(C2,TODAY(),"YM") & "m"

Pitfall: Do not use "MD". Microsoft's own documentation carries a warning against it: because it ignores months and years, it can return a negative number when the end day is earlier in the month than the start day. "YM" and "YD" are safe. "MD" is a known defect that was never fixed, only documented.

Two more things DATEDIF will do to you: it returns #NUM! if the start date is later than the end date — no negatives, ever — and the unit argument must be quoted text.

Fractional years: YEARFRAC. Where DATEDIF truncates, YEARFRAC gives you the decimal:

=YEARFRAC(C2, TODAY(), 1)

Result: 0.57 rather than 0. The third argument is the day-count basis, and 1 (actual/actual) is the one to use unless a finance team has told you otherwise — the default of 0 is the US 30/360 convention, which assumes every month has thirty days and will quietly disagree with a calendar by a day or two.

6) Working Days: NETWORKDAYS and WORKDAY

🎯 Scenario: "Payment is due within 20 working days." "How many business days did that invoice actually take?" Neither question can be answered by adding or subtracting.

These two functions are mirror images:

• NETWORKDAYS(start, end, [holidays]) — count the working days between two dates.
• WORKDAY(start, days, [holidays]) — find the date n working days away.

Counting:

=NETWORKDAYS(C2, E2)

Result: 20 for INV-2041, against 27 calendar days. Note that NETWORKDAYS is inclusive of both ends — if both are working days, both are counted. A start and end on the same Tuesday returns 1, not 0.

Projecting forward:

=WORKDAY(C2, 20)

WORKDAY is exclusive of the start, which is the behaviour you want: "20 working days from today" should not count today.

Holidays are the whole point. Both functions take an optional final argument: a range of dates to skip on top of weekends.

1. Put the public holidays for your region in a column on a Lists sheet.
2. Convert it to a Table with Ctrl + T and name it tblHolidays so it grows.
3. Reference it in every calculation:

=NETWORKDAYS(C2, E2, tblHolidays[Date])
=WORKDAY(C2, 20, tblHolidays[Date])

Without that argument, both functions confidently count Christmas Day as a working day. This is the single most common reason a delivery estimate built in Excel is wrong by two or three days.

When the weekend isn't Saturday and Sunday, use the .INTL variants, which insert a weekend argument:

=NETWORKDAYS.INTL(C2, E2, 7, tblHolidays[Date])
=WORKDAY.INTL(C2, 20, "0000011", tblHolidays[Date])

The string form handles anything — "0001000" for an operation that shuts on Thursdays, "0000000" for a seven-day plant where only the holiday list applies.

Pitfall: NETWORKDAYS will happily return a negative number if you pass the dates in the wrong order. It is not an error and nothing highlights it, so a "days taken" column full of negatives means the arguments are reversed, not that time ran backwards.

7) Times, and What Happens After 24 Hours

🎯 Scenario: A timesheet with a start time and an end time. You need the hours worked per shift, a weekly total, and a decimal figure to multiply by an hourly rate.

Because time is a fraction of a day, duration is subtraction just like dates:

=end - start

Three things then go wrong, in order.

Problem 1: the total resets at 24 hours. Sum a week of shifts and 42:30 displays as 18:30, because the standard h:mm format shows a time of day and a day is where it wraps. The number underneath is correct — only the display is wrong.

The fix is a custom format, not a formula. Select the total, press Ctrl + 1, choose Custom, and enter:

[h]:mm

The square brackets mean "do not roll over". [h]:mm shows 42:30. [m] gives total minutes, [s] total seconds. This one format solves the majority of timesheet complaints in Excel.

Problem 2: shifts that cross midnight. A shift from 22:00 to 06:00 subtracts to -0.667, and negative times display as ##### in the 1900 date system regardless of column width. Wrap it in MOD:

=MOD(end - start, 1)

Result: 8:00. MOD adds a whole day back to any negative result and leaves positive ones untouched, so the same formula works for every shift on the sheet.

Problem 3: you cannot multiply a time by a pay rate. 8:00 is 0.3333, so multiplying by 20 gives 6.67, not 160. Convert to decimal hours first:

=(MOD(end - start, 1)) * 24

Result: 8, a plain number you can multiply, average and sum like any other. Format the cell as Number — inherited time formatting on the result is what makes people think this trick does not work.

Two more that come up constantly:

=INT(A2)                       the date part of a timestamp
=MOD(A2,1)                     the time part of a timestamp
=MROUND(A2, TIME(0,15,0))      round to the nearest quarter hour
=A2 + TIME(1,30,0)             add one hour thirty

TIME(hours, minutes, seconds) wraps at 24 hours the way DATE wraps at 12 months, so TIME(25,0,0) is 01:00 — for adding more than a day, add 1 per day instead.

Pitfall: Typing 1.30 into a cell you meant to hold an hour and a half stores the number 1.3. Times must be typed with a colon — 1:30 — or they are just numbers that will silently be averaged with real durations.

8) Grouping and Reporting by Date

🎯 Scenario: A monthly summary of invoiced value that keeps working when new rows arrive, without a helper column and without a PivotTable.

The instinct is to add a "Month" column with TEXT. Do not — section 3 explained why the sort collapses. Group on a real date instead.

A month bucket is the first of the month:

=EOMONTH(C2,-1)+1

Format that column as mmm yyyy and it reads as Jan 2026 while remaining a genuine date underneath, sortable and chart-friendly.

Totalling a month without a helper column at all. Put the first of the month in H2 and use two criteria that fence the month in:

=SUMIFS($F$2:$F$9, $C$2:$C$9, ">="&H2, $C$2:$C$9, "<="&EOMONTH(H2,0))

Result: The total invoiced in that month. Drag H2 down a column of month starts and the whole report builds itself.

The critical detail is the &. A date criterion must be built by concatenation, because the criteria argument is text:

Rolling windows use TODAY() as the anchor, so the report moves on its own:

=SUMIFS($F$2:$F$9, $C$2:$C$9, ">="&TODAY()-90, $C$2:$C$9, "<="&TODAY())
=SUMIFS($F$2:$F$9, $C$2:$C$9, ">="&EOMONTH(TODAY(),-3)+1, $C$2:$C$9, "<="&EOMONTH(TODAY(),-1))

The first is the last 90 days. The second is the last three complete months, which is usually what a board pack means and rarely what it gets.

Counting what is open right now:

=COUNTIFS($E$2:$E$9, "", $D$2:$D$9, "<"&TODAY())

Result: The number of invoices with no payment date and a due date in the past. One formula, no filtering, correct tomorrow as well.

Pitfall: If your date column contains timestamps rather than clean dates, "<="&EOMONTH(H2,0) quietly excludes everything that happened on the last day of the month after midnight. Strip the time with INT at the source, or fence the upper bound with "<"&EOMONTH(H2,0)+1 instead.

9) When Your Dates Are Text

🎯 Scenario: You exported from a system, the dates look perfect, and every date formula returns #VALUE! or zero.

This is the most common date problem in Excel, and it is invisible by design — a text string that reads 09/01/2026 looks identical to a date that reads 09/01/2026.

Three ways to tell:

1. Alignment. With default formatting, real dates and numbers align right; text aligns left. A column of left-aligned dates is a column of text.
2. =ISNUMBER(C2). FALSE means text. This is the definitive test.
3. The General format check. Ctrl + Shift + ~ turns a real date into a serial number. Text does not change at all.

Three ways to fix it, in ascending order of stubbornness:

DATEVALUE converts a text date that matches your regional settings:

=DATEVALUE(C2)

It returns a serial number, so format the result as a date. It returns #VALUE! on anything it does not recognise — including, frustratingly, a text date in the other region's order.

Arithmetic coercion works when Excel would have recognised the text anyway, and is shorter:

=C2*1
=C2+0
=VALUE(C2)

Text to Columns is the one that always works, and almost nobody reaches for it because it lives under a wizard:

1. Select the column.
2. Data → Text to Columns.
3. Next, Next — no delimiter matters here.
4. On step 3, choose Date and pick the order the source uses: DMY, MDY, YMD.
5. Finish.

That fourth step is the whole point: you are telling Excel how to read the text, rather than hoping it guesses. It converts in place, handles a whole column at once, and rescues the ambiguous cases the other two methods cannot.

For genuinely unparseable formats — 20260109 as a number, or 09-JAN-26 — assemble the date yourself:

=DATE(LEFT(C2,4), MID(C2,5,2), RIGHT(C2,2))

Result: 20260109 becomes a real 9 January 2026. DATE accepts text arguments that look like numbers, so no VALUE wrapper is needed.

Pitfall: The half-converted column is the dangerous outcome. When an import contains a mixture, Excel converts the unambiguous rows (day > 12) and leaves the ambiguous ones as text — so 13/01/2026 becomes a date and 09/01/2026 stays text, in the same column. A SUMIFS over that column returns a number that is wrong rather than an error. Always check with =COUNT(C2:C9) against =COUNTA(C2:C9): the two should match.

Quick Checklist (Before You Trust a Date Column)

• =COUNT(range) equals =COUNTA(range) — every populated cell is a real date, not text
• Literal dates in formulas are written with DATE(y,m,d), never as a typed string
• Month arithmetic uses EDATE / EOMONTH, not +30
• Month grouping is on a real date formatted mmm yyyy, not a TEXT label
• Date criteria in SUMIFS are built with &, not typed inside the quotes
• Any working-day calculation passes a holiday range as its final argument
• WEEKDAY calls specify a return type — 2 for Mon=1
• Duration totals are formatted [h]:mm, and overnight shifts are wrapped in MOD
• Rates are multiplied against duration * 24, not against the time value
• No DATEDIF unit is "MD"
• Timestamps are stripped with INT before grouping or comparing

Common Pitfalls Summary

1. Dates that are text: they look right, sort wrong, and are excluded from every SUMIFS. Check with ISNUMBER.
2. A half-converted import column: unambiguous rows become dates, ambiguous ones stay text. Compare COUNT with COUNTA.
3. Typing "01/03/2026" in a formula: means March in London and January in New York. Use DATE(2026,3,1).
4. Adding 30 days for "one month": wrong in February, wrong in every 31-day month. Use EDATE.
5. ">=H2" as a SUMIFS criterion: searches for the text "H2". It needs ">="&H2.
6. Timestamps in a date column: <= month end silently drops the last day. Strip with INT.
7. Grouping by TEXT(date,"mmm yyyy"): sorts alphabetically. April leads the year.
8. NETWORKDAYS without a holiday list: every public holiday counts as a working day.
9. Reversed arguments in NETWORKDAYS: returns a negative count and no warning.
10. DATEDIF with "MD": can return a negative number. Microsoft documents this and does not fix it.
11. DATEDIF with the dates backwards: #NUM!, not a negative result.
12. A time total showing 18:30 instead of 42:30: the format wrapped at 24 hours. Use [h]:mm.
13. An overnight shift showing #####: negative time. Wrap in MOD(end-start,1).
14. Multiplying a time by an hourly rate: multiply by 24 first, or you get one twenty-fourth of the pay.
15. TODAY() as a permanent record: it is volatile and moves every day. Use Ctrl + ; for a fixed stamp.

Conclusion

Every awkward date problem in Excel resolves into the same sentence: it is a number, and the format is a costume. Sorting fails because the value is text. Totals reset because the format wraps. Rates come out twenty-four times too small because the value is a fraction of a day. None of those are date problems; they are all the serial number showing through.

Learn the four functions that do the work no arithmetic can — EOMONTH and EDATE for months, NETWORKDAYS and WORKDAY for business days — and the rest is addition and subtraction you already know how to do.

If you take three habits away: build literal dates with DATE(), check any column you did not type yourself with ISNUMBER, and give every working-day formula a holiday list. Those three cover almost everything that goes wrong between a date being entered and a report being trusted.

If you want practice, try the date exercises in the app — each one starts from a real ledger with real month ends.