Share via

How to correctly calculate an amount due using dates in excel

Rob P 0 Reputation points
2026-03-03T23:08:14.36+00:00

Hi,

I am trying to correctly calculate an amount due depending on when in the month the amount due normally falls. If the date is past the 28th and there are less days in the previous month than the amount due date then it will be the last calendar day, if the date is on or before the 28th then it will fall on that day. For example, if the due date is 30th, then for February I would expect 1 day from January and 28 days in Feb, so 29 days of (annual fee/365.25) paid. I attached an example file, I hope this makes sense.

I have added some clarity in the file - cannot upload excel file so have created a pdf instead.example file 03.03.2026.pdf

Many thanks in advance.

Microsoft 365 and Office | Excel | Other | Windows
0 comments

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Kal-D 7,365 Reputation points Microsoft External Staff Moderator
    2026-03-25T20:53:18.45+00:00

    Hi Rob P,

    A clean way to handle this is to calculate the number of days between the previous adjusted due date and the current adjusted due date, where any “due day” that doesn’t exist in a month is treated as that month’s last day (for example, “30th of February” becomes Feb 28/29).

    Using your layout:

    • D7 = annual amount due
    • D8 = daily amount due (=D7/365.25)
    • D9 = normal due day of month
    • D11 = day count
    • D10 = amount due
    • A1 = any date in the billing month being calculated (replace with your actual “billing month” date cell)

    Day count (D11)

    Paste this into D11:

    =MIN($D$9,DAY(EOMONTH(A1,0)))-MIN($D$9,DAY(EOMONTH(A1,-1)))+DAY(EOMONTH(A1,-1))

    Amount due (D10)

    Then in D10:

    =D11*$D$8

    Example: If the due day is 30 and the billing month is February, the formula effectively compares 30 Jan with 28 Feb (or 29 Feb in a leap year), so the day count is 29, which matches the prorated expectation you described.

    If you’d like, share what cell you’re using for the “billing month” date (the equivalent of A1), and I can map the references so you can fill the formula down cleanly for a full year.

    Please let me know if this proves useful to you, or if you would like further assistance.

    I'm looking forward to your reply.

    If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment”.   

    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    Was this answer helpful?

  2. AI answer

    2026-03-03T23:08:33.6666667+00:00

    Use Excel’s date arithmetic to derive the “effective” due date each month, then multiply the number of days in the period by the daily rate (annual fee ÷ 365.25).

    Assume:

    • Annual fee in cell B2
    • Nominal due day (e.g., 30) in cell C2
    • Month and year for the period in cells D2 (month as number 1–12) and E2 (year)
    1. Build the effective due date for the month (last calendar day if the nominal day does not exist in that month):
    =MIN(DATE(E2,D2,C2),EOMONTH(DATE(E2,D2,1),0))

    Explanation using the DATE function:

    • DATE(E2,D2,C2) builds the nominal due date (e.g., 2026-02-30). If the day is too large, Excel automatically rolls it into the next month as described for the DATE function.
    • EOMONTH(DATE(E2,D2,1),0) returns the last day of the month.
    • MIN(...) forces the due date to be the earlier of the nominal date and the last day of the month, so for February with a due day of 30, the result is 28-Feb (or 29-Feb in a leap year).
    1. Get the previous month’s effective due date:
    =MIN(DATE(E2,D2-1,C2),EOMONTH(DATE(E2,D2-1,1),0))
    1. Calculate the number of days in the period between the two effective due dates:
    =EffectiveDueDateThisMonth - EffectiveDueDatePreviousMonth

    If the effective due dates are in F2 (this month) and G2 (previous month):

    =F2 - G2
    1. Calculate the amount due for the period using 365.25 days per year:
    =(B2 / 365.25) * (F2 - G2)

    This matches the example: for a due date of 30th and February in a non-leap year, the effective dates will give 1 day in January plus 28 days in February, so 29 × (annual fee ÷ 365.25).

    References:

    AI-generated content may be incorrect. Read our transparency notes for more information.

    Was this answer helpful?

Your answer

Answers can be marked as 'Accepted' by the question author and 'Recommended' by moderators, which helps users know the answer solved the author's problem.