Date & Time
Get days in month: leap-year safe month length and daily proration
Combine DAY and EOMONTH(date, 0) to return 28, 29, 30, or 31 for any calendar date and prorate monthly fees accurately.
Why DAY(EOMONTH(date, 0)) handles every month including leap years
EOMONTH(date, 0) jumps to the last calendar day of the same month (months = 0). Wrapping that end-of-month date in DAY(...) extracts its day number—which equals the total number of days in that month.
Because the calendar engine already knows leap-year rules (divisible by 4, except centuries not divisible by 400), February 2024 automatically returns 29 while February 2026 returns 28.
| RefDate (A) | MonthlyFee (B) | Days in Month | Daily Rate |
|---|---|---|---|
| 2024-02-11 | 1450 | 29 | 50.00 |
| 2026-02-11 | 1400 | 28 | 50.00 |
| 2026-04-19 | 1500 | 30 | 50.00 |
Extract month length and prorated daily fee
Under an actual-days-in-month proration policy, divide the monthly amount by the month length. The example gives $1,450 / 29 = $50 per day; contracts can specify a different policy.
=DAY(EOMONTH(A2, 0))Daily proration formula
The formula displays a daily rate rounded to cents. For a prorated bill, usually retain the unrounded rate until multiplying by billable days, then round the final amount according to the billing policy. Rounding each day first can change the total.
=ROUND(B2 / DAY(EOMONTH(A2, 0)), 2)Try the example
Use the original values or change them to test your understanding.