# How to prorate a monthly amount by days in Excel

Multiply by the share of the month used: `=C2*(EOMONTH(B2,0)-B2+1)/DAY(EOMONTH(B2,0))`.

**Excel:** `=C2*(EOMONTH(B2,0)-B2+1)/DAY(EOMONTH(B2,0))` (Every current version)

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

## Example

Sample data (row 1 is the header):

| A | B | C | D |
| --- | --- | --- | --- |
| Unit | Move-in | Monthly rent | First month rent |
| Unit 2A | 2026-03-10 | 1850 |  |
| Unit 3C | 2026-02-15 | 1600 |  |
| Unit 1B | 2026-04-01 | 1425 |  |
| Unit 4D | 2026-06-30 | 2100 |  |
| Unit 5A | 2026-08-20 | 1975 |  |

Formula in D2, filled to D6: `=C2*(EOMONTH(B2,0)-B2+1)/DAY(EOMONTH(B2,0))`

Result: $1,312.90; $800.00; $1,425.00; $70.00; $764.52

## How it works

`EOMONTH(B2,0)-B2+1` is the number of days from the move-in date to the month end, counting the move-in day. Dividing by the days in the month, `DAY(EOMONTH(B2,0))`, gives the share of the month, and multiplying by the rent gives the prorated amount: 22 of March's 31 days for Unit 2A.

## Other ways

- `=ROUND(C2/DAY(EOMONTH(B2,0))*(EOMONTH(B2,0)-B2+1),2)`: Rounded to cents, as an invoice needs. (Rounded to the cent.)
- `=C2*12/365*(EOMONTH(B2,0)-B2+1)`: A yearly daily rate, so a day costs the same in every month. (Daily rate based on 365 days a year.)

## Common mistakes

- Dividing by 30 for every month overcharges in February and undercharges in 31-day months. Check what the lease says.
- Without the +1 the move-in day is free.

## Questions

**How do I prorate the last month for a move-out?**

Count from the 1st to the move-out date: `=C2*DAY(B2)/DAY(EOMONTH(B2,0))`.

**How do I prorate a yearly amount?**

`=annual*days/365`, using 366 in a leap year if your contract says so.

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/prorate-a-monthly-amount-by-days