cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

DATEDIFF FUNCTION

Good Day!

Asking for help in 

Date Difference function.

DHAGZ_0-1605858738472.png

 



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 

DHAGZ_1-1605858738475.png

 




thanks and advance

3 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
Microsoft
Microsoft

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

View solution in original post

Highlighted
Microsoft
Microsoft

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; 

 DateDiff.png

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

View solution in original post

Highlighted
Microsoft
Microsoft

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

View solution in original post

6 REPLIES 6
Highlighted
Microsoft
Microsoft

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

View solution in original post

Highlighted
Frequent Visitor

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


Highlighted
Microsoft
Microsoft

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

Highlighted
Frequent Visitor

🙂 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.

Highlighted
Microsoft
Microsoft

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; 

 DateDiff.png

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

View solution in original post

Highlighted
Microsoft
Microsoft

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

View solution in original post

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

News & Announcements

Community Blog

Stay up tp date on the latest blogs and activities in the community News & Announcements.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Community Highlights

Community Highlights

Check out the Power Platform Community Highlights

Top Solution Authors
Top Kudoed Authors
Users online (7,206)