DATEDIFF FUNCTION

[DHAGZ]

Good Day!

Asking for help in 

Date Difference function.

My concern is 
how to get or how to convert Excel formula into PowerApps formula like this

=DATEDIF(StartDate,EndDate+1,"Y")
=DATEDIF(StartDate,EndDate+1,"YM")
=DATEDIF(StartDate,EndDate+1,"MD")

Example (1) :
Start Date: 11/1/2011 
End Date: 3/31/2012

and the result should be like this
Year(s) = 0 / Month(s) = 5/ Day(s) = 0

Example (2) :
Start Date: 2/23/2010 
End Date: 5/31/2010

and the result should be like this
Year(s) = 0 / Month(s) = 3 / Day(s) = 9


hope you can help me 

thanks and advance

[RusselThomas · Accepted solution]

Hi @DHAGZ ,

If I'm reading this right, then there's a couple of ways to do it depending on whether you want totals for each unit, or a single duration value.  I'll assume you want a single duration value, as totals for each unit is just DateDiff() once for each unit.

DateDiff takes three parameters;

DateDiff(start_date, end_date, UNITS)

where Units can be Years, Quarters, Months, Days, Hours, Minutes, Seconds or Milliseconds.  The unit you specify will be the result it returns.

To get a duration, we want to know the difference between two dates, not as a unit, but as a representation of the total Days in Years, Months and Days.  To get that, and make sure we're honoring the calendar between the dates, we need to do a couple of steps - working back from years to months and then days, iterating as we go.

First, the logic:

1: Do a straightforward DateDiff for Years

2: Add Years to the start date, so you can then get the remaining months

3: Do a straightforward DateDiff for Months

4: Add Months to the start date so you can get the remaining days

5: Put them all together to get Duration in Years, Months, Days

Then the build:

Put two datepickers on the screen, call the first one start_date and the second one end_date

Then the Expression:

I've spread this out so you can see each step, and I'm using With() to keep all my variables inside the expression, so it looks like a lot and very complicated - but it's actually not.  You can probably condense this, and someone cleverer than me can likely show you a shortcut, but here's the formula that worked for me - you can put this into a Text Label Text property:

With({
        evarYearsValue: DateDiff(start_date.SelectedDate, end_date.SelectedDate, Years) //get the years between the dates
        },
        With(
        {
            evarYearsAddedDate: DateAdd(start_date.SelectedDate, evarYearsValue, Years) //add the years to the start date 
            },
            With(
            {
                evarMonthsRemainder: DateDiff(evarYearsAddedDate, end_date.SelectedDate, Months) //so you can get the remaining months
                },
                With(
                {
                    evarMonthsValue: DateDiff(start_date.SelectedDate, end_date.SelectedDate, Months) //Now get the months between the two dates
                    },
                    With(
                    {
                        evarMonthsAddedDate: DateAdd(start_date.SelectedDate, evarMonthsValue, Months) //add the months to the start date 
                        },
                        With(
                        {
                            evarDaysValue: DateDiff(evarMonthsAddedDate, end_date.SelectedDate, Days) + 1//so you can get the remaining days  ---NOTE:  +1 if you want to include the final day as a full day
                            },

            "Year(s) =" & evarYearsValue & " / Month(s) = " & evarMonthsValue & " / Day(s) = " & evarDaysValue 
        ))
    ))
))

 Note at the end on the Days - I've added 1 day to the result to include the last day - so if you don't want to include the last day in your calc, then remove the +1.

Hope this helps,

RT

[DHAGZ]

Thank you for your Reply @RusselThomas  🙂

Example (2) :
Start Date: 2/23/2010 
End Date: 5/31/2010

Year(s) = 0 / Month(s) = 3 / Day(s) = 9
I've got a correct answer 

But in 
Example (1) :
Start Date: 11/1/2011 
End Date: 3/31/2012

Start Date < End Date

and the result should be like this
Year(s) = 0 / Month(s) = 5/ Day(s) = 0
the result is always Year(s) = 1 / Month(s) = 4/ Day(s) = 31

[RusselThomas]

Hi @DHAGZ ,

Too true - I see the problem - rolling over years or months that aren't actually 365 or 30+ days.  Instead of coming up with 0 years between 2011 and 2012 when the difference might only be 5 months, it's literally subtracting 2011 from 2012 and coming up with 1 year.

My original approach is flawed, so we might have to take this from the opposite direction - let me think about it and test some outcomes and I'll respond later today 🙂

Kind regards,

RT

[DHAGZ]

🙂 Thankyou Sir @RusselThomas ..

I've try this based on your formula...
and I got the correct Answer 
in Y=0 /M=5 /D=0

With({
evarYearsValue: DateDiff(start_date.SelectedDate, end_date.SelectedDate, Years)-1 //get the years between the dates
},
With(
{
evarYearsAddedDate: DateAdd(start_date.SelectedDate, evarYearsValue, Years) //add the years to the start date
},
With(
{
evarMonthsRemainder: DateDiff(evarYearsAddedDate, end_date.SelectedDate, Months)+1 //so you can get the remaining months
},
With(
{
evarMonthsValue: DateDiff(start_date.SelectedDate, end_date.SelectedDate, Months)+1 //Now get the months between the two dates
},
With(
{
evarMonthsAddedDate: DateAdd(start_date.SelectedDate, evarMonthsValue, Months) //add the months to the start date
},
With(
{
evarDaysValue: DateDiff(evarMonthsAddedDate, end_date.SelectedDate, Days)+1//so you can get the remaining days ---NOTE: +1 if you want to include the final day as a full day
},

"Year(s) =" & evarYearsValue & " / Month(s) = " & evarMonthsValue & " / Day(s) = " & evarDaysValue
))
))
))})

But when I try This.

UpdateContext({LSSTHAN:
With({
evarYearsValue1: DateDiff(start_date.SelectedDate, end_date.SelectedDate, Years) //get the years between the dates
},
With(
{
evarYearsAddedDate1: DateAdd(start_date.SelectedDate, evarYearsValue1, Years) //add the years to the start date
},
With(
{
evarMonthsRemainder1: DateDiff(evarYearsAddedDate1, end_date.SelectedDate, Months) //so you can get the remaining months
},
With(
{
evarMonthsValue1: DateDiff(start_date.SelectedDate, end_date.SelectedDate, Months) //Now get the months between the two dates
},
With(
{
evarMonthsAddedDate1: DateAdd(start_date.SelectedDate, evarMonthsValue1, Months) //add the months to the start date
},
With(
{
evarDaysValue1: DateDiff(evarMonthsAddedDate1, end_date.SelectedDate, Days)+1 //so you can get the remaining days ---NOTE: +1 if you want to include the final day as a full day
},

"Year(s) =" & evarYearsValue1 & " / Month(s) = " & evarMonthsValue1 & " / Day(s) = " & evarDaysValue1
))
))
))});
UpdateContext({GRTHAN:
With({
evarYearsValue: DateDiff(start_date.SelectedDate, end_date.SelectedDate, Years)-1 //get the years between the dates
},
With(
{
evarYearsAddedDate: DateAdd(start_date.SelectedDate, evarYearsValue, Years) //add the years to the start date
},
With(
{
evarMonthsRemainder: DateDiff(evarYearsAddedDate, end_date.SelectedDate, Months)+1 //so you can get the remaining months
},
With(
{
evarMonthsValue: DateDiff(start_date.SelectedDate, end_date.SelectedDate, Months)+1 //Now get the months between the two dates
},
With(
{
evarMonthsAddedDate: DateAdd(start_date.SelectedDate, evarMonthsValue, Months) //add the months to the start date
},
With(
{
evarDaysValue: DateDiff(evarMonthsAddedDate, end_date.SelectedDate, Days)+1//so you can get the remaining days ---NOTE: +1 if you want to include the final day as a full day
},

"Year(s) =" & evarYearsValue & " / Month(s) = " & evarMonthsValue & " / Day(s) = " & evarDaysValue
))
))
))});

UpdateContext({Rslt:If(start_date.SelectedDate < end_date.SelectedDate,GRTHAN,
If( start_date.SelectedDate > end_date.SelectedDate ,LSSTHAN
))

})

the Answer is Not Correct.

[RusselThomas · Accepted solution]

Hi @DHAGZ ,

I spent some time looking at the problem, and there's a catch.  Again, I'm really hoping I'm understanding what you're trying to do correctly, but the best way I can explain it is as follows;

Calculating the days between dates is easy - days are a common denominator.  Converting a value of days into a duration that reads "n Years, x Months, y Days" that is independent of the calendar is not so easy - here's how I see the problem taking an example of trying to work out the duration between 12th Feb 2020 and the 23rd May 2020; 

 

Hopefully a) this makes sense and b) is not blatantly wrong.  This stuff hurts the brain to think on it too long 🙂

So the question is - is this duration what you're trying to achieve?

If so, then the next question is whether the accuracy will be a problem - rounding and dividing by 30 will introduce variance and you'll never be 100% accurate - but that's because you never know what months are involved between two dates - but unless someone has a better idea, it's the best I can come up with.

RT

[RusselThomas · Accepted solution]

Hi @DHAGZ ,

Just following up with another stab at a formula that assumes an average of 30 days per month across a year - might be worth a try;

With({totalDays: DateDiff(start_date.SelectedDate, end_date.SelectedDate, Days)+1},
    With({totalYears: RoundDown(totalDays/365, 0), remainingDaysFromYearsRollUp: Mod(TotalDays, 365)},
        With({MonthsInRemainingDays: RoundDown(remainingDaysFromYearsRollUp/30, 0), remainingDaysFromMonthsRollUp: Mod(remainingDaysFromYearsRollUp, 30)},

        totalYears & " years / " & MonthsInRemainingDays & " months / " & remainingDaysFromMonthsRollUp & " days"
        )))

Obviously, not all months have 30 days in them, so expect variance depending on your inputs and longer duration between dates.

If you want to try and increase accuracy you can calculate the full calendar months between the two dates by cutting off the start and end date months and just doing a DateDiff on Months in between, factoring in the remaining days afterwards - but this gets quite involved, (you have to figure out if the remaining days on either side also constitute more than a month), so you may want to decide if it's worth the effort before doing it.

At the end of the day, there's no perfect answer that works 100% for all scenarios, so I guess it's about finding something that works for 99% of your scenarios and living with the variance.

Hope this helps,

RT

[jk123455]

I just ran into the same issue.. DATEDIFF() in PowerApps sucks.. plain and simple. It's a flawed function.
You shouldn't have to do work arounds to get the correct results.

[jk123455]

This will not work.. as soon as your start comparing days, months, years over a wider span your result will be more and more flawed... especially with leap years and months of 31.

[Suchithra_A]

I found expected solution with the below expression:

With({
varYearsValue: DateDiff(startdate.SelectedDate, enddate.SelectedDate,TimeUnit.Years)-1
},
With(
{
varYearsAddedDate: DateAdd(startdate.SelectedDate, varYearsValue,TimeUnit.Years)
},
With(
{
varMonthsRemainder: DateDiff(varYearsAddedDate, enddate.SelectedDate,TimeUnit.Months)
},
With({
varYearsValue:If(varMonthsRemainder>=12,varYearsValue+1,varYearsValue),
varMonthsRemainder:If(varMonthsRemainder>=12,varMonthsRemainder-12,varMonthsRemainder)
},
varYearsValue & "." & varMonthsRemainder
))
)
)

Result : 

startdate.SelectedDate =9/30/2020

enddate.SelectedDate=4/1/2023

Ans: 2.7