Once you see dates as numbers, the calendar functions turn out to be clever integer arithmetic. The messy month-length and weekend rules are handled for you.
How do EDATE and EOMONTH work?
EDATE: same day, N months away. EDATE(start, months) returns the date that is months calendar months from start. When the target month is shorter, it clamps to month-end. =EDATE("2025-01-31", 1) returns 28 Feb 2025. There's no 31 February, so it snaps to the last day. Use it for anything on a monthly cadence: EMI dates, subscription renewals, or EDATE(hire_date, 6) for a probation-end date. =EDATE("2026-08-31", -2) runs backward two months to 30 Jun 2026.
EOMONTH, the last day of a month. EOMONTH(start, months) returns the end of the month months away. =EOMONTH("2026-02-10", 0) returns 28 Feb 2026, because 2026 isn't divisible by 4 and so isn't a leap year. The same call on a 2024 date, =EOMONTH("2024-02-10", 0), returns 29 Feb 2024. =EOMONTH("2026-03-18", 0) returns 31 Mar 2026.
You'll use two idioms forever. The first day of a date's month is =EOMONTH(A2, -1) + 1. And end-of-month payment terms become trivial. An invoice dated 18 Mar 2026 on "net, end of month + 15 days" terms is due =EOMONTH("2026-03-18", 0) + 15 = 15 Apr 2026. On plain net-30 (=DATE(2026,3,18)+30) it's due 17 Apr 2026. The two term structures differ by two days here. On a receivables ledger, a gap like that is what decides the ageing bucket.
Advertisement
How do you count working days with NETWORKDAYS?
NETWORKDAYS and NETWORKDAYS.INTL: counting working days. NETWORKDAYS(start, end, [holidays]) counts the working days between two dates, including both ends. It skips Saturdays and Sundays, plus any dates in an optional holiday list. NETWORKDAYS.INTL(start, end, [weekend], [holidays]) adds a weekend argument, so you can model non-Western working weeks.
Take a US bi-weekly pay period, Monday 17 Nov 2025 through Friday 28 Nov 2025, with Thanksgiving (Thursday 27 Nov 2025) as a holiday. There are ten weekdays in that span and one of them is the holiday. So =NETWORKDAYS.INTL("2025-11-17", "2025-11-28", 1, {"2025-11-27"}) returns 9 working days. The 1 is the default Saturday/Sunday weekend code. Drop the holiday and it returns 10.
Now model a six-day working week, with Sunday the only day off. The weekend string is "0000001": seven characters running Monday to Sunday, where 1 marks a weekend day. =NETWORKDAYS.INTL("2025-11-17", "2025-11-28", "0000001", {"2025-11-27"}) returns 10, because only Thanksgiving and the one Sunday, 23 Nov, come out. Working-day counts drive expected hours (9 × 8 = 72 hours), accruals and SLA calculations. NETWORKDAYS.INTL requires Excel 2010+.
How do DATEDIF and YEARFRAC work?
DATEDIF, durations in whole years, months, days. DATEDIF(start, end, unit) is undocumented but universally supported, and it's the function for age and tenure in a form people can read. Its units: "Y" = complete years, "M" = complete months, "D" = days, "YM" = months after the whole years, "MD" = days after the whole months. Take an employee hired 15 Mar 2019, measured as of today, 20 Jul 2026. =DATEDIF("2019-03-15", "2026-07-20", "Y") = 7 complete years, "YM" = 4 and "MD" = 5, which reads as "7 years, 4 months, 5 days". "M" returns 88 total months. The argument order is unforgiving. If start doesn't come before end, it errors. And because the function is unadvertised, Excel shows you no argument tooltip, so memorise the three-argument shape.
YEARFRAC, a duration as a fraction of a year, for interest. YEARFRAC(start, end, [basis]) returns the year-fraction between two dates under a day-count basis, the same conventions bond and loan markets use: 0 = US 30/360, 1 = actual/actual, 2 = actual/360, 3 = actual/365, 4 = European 30/360. Change the basis and you change the number. Under 30/360 (basis 0), a 15 Jan to 15 Jul span comes to half a year. Simple interest on ₹10,00,000 at 8% for that period is =1000000 * 0.08 * YEARFRAC("2025-01-15","2025-07-15",0) = 1000000 × 0.08 × 0.5 = ₹40,000. Always state your basis when you quote an accrual, the same way you state your EBIT construction in ratio work.
How do you find the day of the week with WEEKDAY?
WEEKDAY, which day of the week. WEEKDAY(date, [return_type]) returns a number for the day. The default return_type 1 makes Sunday = 1 … Saturday = 7. With return_type 2, Monday = 1 … Sunday = 7, the ISO style analysts prefer. =WEEKDAY("2026-01-01", 2) returns 4, which tells you New Year's Day 2026 is a Thursday. Pair it with conditional formatting or an IF to flag weekend-dated transactions. They're often data-entry errors or back-dated entries, and worth a second look.