Spreadsheet Practice

How to prorate a monthly amount by days in Excel

Formula (Excel and Google Sheets)

=C2*(EOMONTH(B2,0)-B2+1)/DAY(EOMONTH(B2,0))

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

Result on the example
one result per row in D2, filled down to D6
Works in
Every current version
Google Sheets
Same formula

See it work

The formula is already in D2, filled down to D6. Change any input and the result updates, or edit the formula to experiment.

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.

=C2*(EOMONTH(B2,0)-B2+1)/DAY(EOMONTH(B2,0))

Result1312.903226

  1. C2$1,850

    The value in C2.

  2. B23/10/2026

    The value in B2.

  3. EOMONTH(B2,0)3/31/2026 (46112)

    EOMONTH: Last day of the month, months away.

    • start_dateB23/10/2026
    • months0
  4. EOMONTH(B2,0)-B221

    The - operator subtracts.

  5. EOMONTH(B2,0)-B2+122

    The + operator adds.

  6. C2*(EOMONTH(B2,0)-B2+140700

    The * operator multiplies.

  7. EOMONTH(B2,0)3/31/2026 (46112)

    EOMONTH: Last day of the month, months away.

    • start_dateB23/10/2026
    • months0
  8. DAY(EOMONTH(B2,0))31

    DAY: Day of the month, 1–31.

    • serial_numberEOMONTH(B2,0)3/31/2026 (46112)
  9. C2*(EOMONTH(B2,0)-B2+1)/DAY(EOMONTH(B2,0))1312.903226

    The / operator divides.

In Google Sheets

The same formula works in Google Sheets: =C2*(EOMONTH(B2,0)-B2+1)/DAY(EOMONTH(B2,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.

FormulaResultWhen to use it
=ROUND(C2/DAY(EOMONTH(B2,0))*(EOMONTH(B2,0)-B2+1),2)$1,312.90, $800.00, $1,425.00…Different resultRounded to cents, as an invoice needs. Rounded to the cent.
=C2*12/365*(EOMONTH(B2,0)-B2+1)$1,338.08, $736.44, $1,405.48…Different resultA 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.

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.

Prorate a monthly amount by days

In D2, prorate the monthly rent for the days from the move-in date to the end of that month, counting the move-in day. Fill down to D6.

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.

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.