Date Formulas for Month Ends and Business Days in Excel
Excel date formulas for month starts and ends, adding months, business days with holidays, fiscal years and age, with the edge cases that break them.
Most reporting questions are about time. Revenue for the month, invoices due in 30 days, the third business day after close, customers who turned 18 last week. Excel can answer all of them, and each answer has a boundary condition waiting: the 31st, the 29th of February, a bank holiday on a Monday. This article gives the formulas that cover month ends and starts, month arithmetic, business days, quarters and fiscal years, with the cases that catch people. It belongs with Excel formulas data people use every week, and for the database equivalents see date functions in Excel and SQL.
A date is a number
Excel stores a date as a serial number counting days from 1900, and a time as the fraction of a day (Excel stores dates as serial numbers). That makes arithmetic simple: =B2 - A2 is the number of days between two dates. It also means a cell showing 29/10/2026 may hold text, in which case it does not subtract or sort. Check with =ISNUMBER(A2). If it returns FALSE, convert first: =DATE(2026, 10, 29) for known parts, or =DATEVALUE("2026-10-29") for an ISO string, which Excel parses in all locales.
Always format result cells as dates (Ctrl+1, Date). A formula that returns 46324 is correct, and the cell just needs a format.
Month starts and ends
EOMONTH(start_date, months) returns the last day of the month that is months away from the start.
=EOMONTH(A2, 0) last day of A2's month (2026-10-29 -> 2026-10-31)
=EOMONTH(A2, -1) last day of the previous month (2026-09-30)
=EOMONTH(A2, -1) + 1 first day of A2's month (2026-10-01)
=EOMONTH(A2, 0) + 1 first day of the next month (2026-11-01)The "previous month end plus one" idiom is the standard way to get a month start, and it handles every month length and leap years. Avoid DATE(YEAR(A2), MONTH(A2), 1) only if you dislike two functions; it is equally correct.
For a half-open period filter, use the first of the month and the first of the next:
=SUMIFS(D:D, C:C, ">="&EOMONTH(A2,-1)+1, C:C, "<"&EOMONTH(A2,0)+1)This captures timestamps on the last day, because the upper bound is the start of the next month. A filter that says <=EOMONTH(A2,0) compares against midnight at the start of the last day and drops later times that day. The same half-open habit matters in SQL (designing a report query before touching Excel).
Adding months
EDATE(start_date, months) adds whole months.
=EDATE(DATE(2026,1,31), 1) -> 2026-02-28
=EDATE(DATE(2028,1,31), 1) -> 2028-02-29
=EDATE(DATE(2026,10,15), -3) -> 2026-07-15When the target month is shorter, the day clamps to the last day, so January 31 plus one month is February 28 (or 29). Adding another month to that result gives March 28, not March 31, which is a surprise for chains of renewals. To keep the month end as month end, use EOMONTH for the later steps: =EOMONTH(A2, n) when A2 is itself a month end. A subscription that renews "on the 31st or the last day" needs EOMONTH; one that renews "on the 15th" needs EDATE. Write down which is meant.
Adding days is plain arithmetic: =A2 + 30. Adding years: =EDATE(A2, 12*n). The DATE(YEAR(A2)+1, MONTH(A2), DAY(A2)) form turns February 29 into March 1 in a non-leap year, where EDATE(A2, 12) gives February 28.
Business days
NETWORKDAYS(start, end, [holidays]) counts weekdays between two dates, inclusive of both ends, optionally skipping dates in a holiday range.
=NETWORKDAYS(DATE(2026,10,1), DATE(2026,10,31), Holidays)October 2026 has 22 weekdays. If Holidays lists one date on a weekday, the result is 21. WORKDAY(start, days, [holidays]) returns the date days working days after the start:
=WORKDAY(A2, 10, Holidays) 10 working days after A2
=WORKDAY(EOMONTH(A2,0), 3, Holidays) third working day after month endNeither counts the start date as a day added: WORKDAY(date, 1) is the next working day. Negative values go backwards. The holiday list is the part people forget. Keep it in a named range (Holidays) on a sheet that someone owns, by country, and extend it each year. A formula using a stale list returns a plausible wrong date.
NETWORKDAYS.INTL and WORKDAY.INTL take a weekend pattern, for regions where the weekend is Friday and Saturday or where a six-day week applies:
=WORKDAY.INTL(A2, 5, "0000110", Holidays) weekend on Friday and SaturdayThe seven-character string marks Monday to Sunday, with 1 for a non-working day. Hours-based service levels (response within 8 working hours) need more than these functions; build them in code, not in cell formulas.
Quarters, fiscal years and week numbers
A calendar quarter from a date:
="Q" & ROUNDUP(MONTH(A2)/3, 0)A fiscal year starting in April, marked by the year it ends in:
=YEAR(EDATE(A2, 9))Adding nine months shifts April 1 to January 1 of the next calendar year, so dates from April 2026 to March 2027 give 2027. Check it with boundary dates: 31 March 2027 gives 2027, 1 April 2027 gives 2028. For a fiscal quarter, number the months from the fiscal start: =ROUNDUP(MOD(MONTH(A2) - 4, 12) + 1, 0)/3 is easy to get wrong; a small lookup table from month to fiscal quarter is clearer, and a person can read it.
Week numbers differ by definition. WEEKNUM(A2) counts the week with January 1 as week 1; ISOWEEKNUM(A2) follows ISO 8601, where weeks start on Monday and week 1 contains the first Thursday. Dates near the year end can belong to week 52 or 53 of the previous year or week 1 of the next. Pick the definition the business uses and say so on the report.
Age and elapsed time
DATEDIF(start, end, unit) returns complete years ("y"), months ("m") or days ("d"), and the remainders "ym", "md" and "yd".
=DATEDIF(A2, TODAY(), "y") age in complete years
=DATEDIF(A2, B2, "m") complete months between two datesIt is not listed in the function wizard, but it works, and the "md" option has known bugs, so avoid it. For a customer who turned 18, compare dates: =EDATE(A2, 12*18) <= TODAY() states the rule directly. Elapsed time between timestamps: =(B2 - A2) * 24 gives hours, and TEXT(B2 - A2, "[h]:mm") formats durations over 24 hours properly. Times inside a day roll over at midnight, so format durations with square brackets.
TODAY() and NOW() are volatile: they recalculate every time the workbook changes, and a report built on them shows a different answer tomorrow. For a report that must be reproducible, put the as-of date in a cell as a value and refer to it.
Time zones and timestamps
Excel dates have no time zone. A timestamp exported from a database in UTC and read in local time moves late evening events into the next day. Decide the zone, convert in the query or in a helper column (=A2 + 10/24 for a fixed offset, which ignores daylight saving), and label the zone in the header. For anything with daylight saving rules, convert in the database with a proper zone name (handling currency and locale in generated workbooks covers the labelling).
Test the boundaries
Date formulas fail in a few predictable places, so test them there:
The last day of a 31-day, 30-day, 28-day and 29-day month.
December 31 and January 1, for year transitions.
February 29 in a leap year such as 2028.
A holiday on a Monday and one on a Sunday.
Midnight and 23:59:59 on the last day of a period.
Dates before March 1, 1900, where Excel's leap year bug applies (the 1900 leap year bug in Excel).
Put those dates in a small test block with the expected answers beside them, so a change to a formula shows immediately whether it still agrees.
When date formulas are the wrong approach
For one-off calendars on a small sheet, formulas are fine. They are the wrong approach when the calendar has many rules, such as fiscal calendars with 4-4-5 week patterns, regional holidays and cutoffs: keep a calendar table, in the database or in Power Query, with one row per day and columns for fiscal period, business day flag and holiday name, and join to it. A table is easier to audit than a formula, and it is the same table in every report. They are also the wrong approach when dates come in as text from many sources; fix the import so that dates arrive as dates (importing Excel dates correctly). Microsoft documents the functions in the EOMONTH reference and the NETWORKDAYS reference.
