How to get the number of days in a month in Excel
Formula (Excel and Google Sheets)
=DAY(EOMONTH(A2,0))Take the day number of the month's last day: =DAY(EOMONTH(A2,0)).
- Result on the example
- one result per row in
B2, filled down toB7 - Works in
- Every current version
- Google Sheets
- Same formula
See it work
The formula is already in B2, filled down to B7. Change any input and the result updates, or edit the formula to experiment.
How it works
EOMONTH(A2,0) is the last day of the month, and its DAY is the number of days in the month. Leap years are handled: February 2028 has 29 days, but February 2100 has 28, because century years are leap years only when divisible by 400.
In Google Sheets
The same formula works in Google Sheets: =DAY(EOMONTH(A2,0)). We ran it in Google Sheets mode on the same data and it returns the same results.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =EOMONTH(A2,0)-EOMONTH(A2,-1) | 28, 29, 30… | Subtracts last month's end from this month's end. |
| =DAY(DATE(YEAR(A2),MONTH(A2)+1,0)) | 28, 29, 30… | Day 0 of next month is the last day of this month. Works in any version. |
Common mistakes
- Using 30 for a daily rate overcharges in February and undercharges in 31-day months.
- The result can pick up the date format from column A. Format it as a number.
Try it yourself
Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.
Get the number of days in a month
B2, return the number of days in the month of the date in A2, and fill down to B7.Questions
How do I turn a monthly amount into a daily rate?
=amount/DAY(EOMONTH(A2,0)).
How do I get the number of days in the year?
=DATE(YEAR(A2),12,31)-DATE(YEAR(A2),1,0) returns 365 or 366.
Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.