If rule and date

    • New

    Hello
    I am creating a formula to capture a rate per day * number of days of the month, but when it is a full month not to apply the formula

    I mean if it is 28 Feb or 30 or 31 not to calculate the rate per day into Number of days, but to use the amount as is.


    E7 Date

    E9 Amount,

    C13 Number of days of the month (fraction of the month) ; formula of CONCATENATE(DAY(E7)," days"


    I used below formula

    =IF(DAY(EOMONTH(E7,0))>=30,E9,((LEFT(C13,2)*(E9*12/365))))

    the result is always full amount
    and it is not working when it is Feb 28 days?

    How to make the "EOMonth" changeable based on the month in E7 date, then how to make the calculation correct, either full amount on full month or pro-rated by number of days

    Appreciate your help

    Thank you

    Wafaa

    • New

    Based on the description, E7 is the date and E9 appears to be the value for a full month as you want the full value of E9 if it's the end of the month.


    If so then without C13 you can determine the fraction of the month passed using just the date value in E7 with the following:

    =DAY(E7)/DAY(EOMONTH(E7,0))

    When the date is the end of the month, that formula will result in a value of 1.


    Then all you need to do is multiply that factor by E9:

    =DAY(E7)/DAY(EOMONTH(E7,0))*E9

    To get the value proportionate to the number of days in the month relative to the date in E7.


    You could extend this slightly if you want to round the resulting value to 2 decimal places with the following:

    =ROUND(DAY(E7)/DAY(EOMONTH(E7,0))*E9,2)

    • New

    Hello

    Thank you for your prompt reply

    It works logically when I used it, but didn't match my calculation as the rate per day should be E7/30.41666, and not E7/30, which gives high difference . Thank you

    • New

    Why should you divide by 30.41666? No month has that number of days.

    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

    • New

    Also, it will mean that with your formula the last day of any month will equate to a different percentage of the total value than any other day in that month. I don't see why you would want that?

    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

    • New

    Hello

    The average rate per day applies in both leap and regular years,

    The rate per day into number of fraction days of the month, formula becomes void when it is full month it pays full amount without pro-rated fractions.

    ROUND(DAY(E7)/DAY(EOMONTH(E7,0))*E9,2), the formula, you helped with, is so valuable, but unfortunately didn't work with the rate per day.

    Thanks & Regards

    • New

    My point is that if you apply your average, then for February, every day is worth 1/30.41666 of the total apart from the last day of the month which is worth about 3 times as much. I can't see why you would prefer that to actually using the number of days in the relevant month.


    Regardless, all you really need to do to your original formula is test if E7=EOMONTH(E7,0) to see if it's the last day of whichever month.

    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

    • New

    Hi


    Thank you for your support, the average rate per day amount*12/365 disregards whether the month is 30 or 28 31,


    I will keep trying to get it worked.


    Thank you

    • New

    I am aware of that - that is precisely my point. Why do you want that when it makes the value of the last day of any month different to the value of any other day in that month?


    Regardless, I already told you how to work around it.

    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login