# How to get the number of days in a month in Excel

Take the day number of the month's last day: `=DAY(EOMONTH(A2,0))`.

**Excel:** `=DAY(EOMONTH(A2,0))` (Every current version)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B |
| --- | --- |
| Billing month | Days |
| 2026-02-10 |  |
| 2028-02-01 |  |
| 2026-04-30 |  |
| 2026-07-15 |  |
| 2026-11-03 |  |
| 2100-02-14 |  |

Formula in B2, filled to B7: `=DAY(EOMONTH(A2,0))`

Result: 28; 29; 30; 31; 30; 28

## 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.

## Other ways

- `=EOMONTH(A2,0)-EOMONTH(A2,-1)`: Subtracts last month's end from this month's end.
- `=DAY(DATE(YEAR(A2),MONTH(A2)+1,0))`: 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.

## 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.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/days-in-a-month