DATEDIF formula not working

    Hi,


    I am using the DATEDIF formula & it is not considtent with the calculations. I am using it to calculate the amount of months passed, to calculate accruals & the following happens:


    =IF(P17="Accrual",DATEDIF(I17,($F$2),"M"),0)


    where I17 is the end date of last invoice & F2 is the current end of the month (i.e. this month is 30/11/2020)


    I have attached an image where you can see the calculation is wrong. Can someone please help explain why when the end date is Oct 2020 that in the month of Nov 2020, the accrual value is not 1?


    This formula works fine for an accrual which ended June 2020 (correctly calculated 5 months)


    Thanks,


    Mel

    DATEDIF has always had problems with this calculation with months that have different numbers of days in them.

    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

    Quote from rory

    DATEDIF has always had problems with this calculation with months that have different numbers of days in them.

    Do you have any recommendation for a replacement formula? Its strange as Oct-Nov is calculating incorrectly but June-Nov is calculating the correct months or vise versa depending on the formula.


    Or, do you understand the reasons for these inconsistencies so I can try to formulate around it?

    As I said, June and November have the same number of days, so that should work fine; only months with different numbers of days have issues.


    Will the dates always be the end of a 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

    Quote from rory

    As I said, June and November have the same number of days, so that should work fine; only months with different numbers of days have issues.


    Will the dates always be the end of a month?

    Ahh okay that makes sense now, I have a solution thank you! I already have a lookup built in to check whether theres 31 or 30 days in the month so will incorporate this now to ensure it calcs the right days. I misinterpreted your initial comment.


    Thanks,


    Mel

Participate now!

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

Register Yourself Login