Difference in Calculation - Dates | PowerApps vs. Excel

[AkshayManke]

Hi,

I want to calculate year and months from two dates. Actually, I am replicating excel like functionality into PowerApps.

In Excel I have below formula.

Get Month =DATEDIF(B6,B7, "ym")

Get Year =DATEDIF(B6,B7, "y")

Below the output I am getting.

But when i am trying to enter the same dates and calculating them then getting the below results.

I am using the below PowerFX code.

DateDiff(
    Start.SelectedDate,
    End.SelectedDate,
    TimeUnit.Years
) & " Years " & Mod(
    DateDiff(
        Start.SelectedDate,
        End.SelectedDate,
        TimeUnit.Months
    ),
    12 
) & " Months "

Now sure why and where is the problem. I would be really glad to have help on this.

Warm Regards,

Akshay

[ANB]

Hi @AkshayManke Try this:

If(
    "." in Text(
        DateDiff(
            Start.SelectedDate,
            End.SelectedDate,
            TimeUnit.Months
        ) / 12
    ),
    First(
        Split(
            Text(
                DateDiff(
                    Start.SelectedDate,
                    End.SelectedDate,
                    TimeUnit.Months
                ) / 12
            ),
            "."
        )
    ).Value & " Years " & Mod(
        DateDiff(
            Start.SelectedDate,
            End.SelectedDate,
            TimeUnit.Months
        ),
        12
    ) & " Months ",
    DateDiff(
        Start.SelectedDate,
        End.SelectedDate,
        TimeUnit.Months
    ) / 12 & " Years " & Mod(
        DateDiff(
            Start.SelectedDate,
            End.SelectedDate,
            TimeUnit.Months
        ),
        12
    ) & " Months "
)

-----------------------------------------------------------------------------------------------------------------------------

I hope this helps.

Please click Accept as solution ✅ if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs up.👍

Thanks,
ANB

[AkshayManke]

Hi @ANB, thanks a lot for the quick help.. Now the years are correct but still month is showing 10 which should be actually 9.. Can you please suggest what further i should modify to get the accurate result.

Many Thanks again!

[ANB]

Hi @AkshayManke Check this if this can help you: https://powerusers.microsoft.com/t5/Building-Power-Apps/DATEDIFF-FUNCTION/td-p/751453

-----------------------------------------------------------------------------------------------------------------------------

I hope this helps.

Please click Accept as solution ✅ if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs up.👍

Thanks,
ANB

[AkshayManke]

Hi @ANB, i tried that too but the month difference is still there. 

[ANB]

@AkshayManke If you have read the complete post that i gave, in that people are looking similar kind of solution but getting 100% accuracy will be difficult. 

There are lots of factors involved,  like all months are not equal in terms of days. 28, 29, 30 and 31.

I want you read this from above post:

I am not telling that it is not possible but to build the 100% accuracy result, it is require to spent sometime. 

Thanks,

ANB

[AkshayManke]

Thanks @ANB for sharing the information. I would like to keep this thread open to receive more suggestions.