Skip to main content

Notifications

Community site session details

Community site session details

Session Id : J1SltjK+YvKj1LBh8ZS4qF
Power Apps - Building Power Apps
Answered

DATEDIFF FUNCTION

Like (1) ShareShare
ReportReport
Posted on 20 Nov 2020 07:57:47 by 162

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

Categories:
  • RusselThomas Profile Picture
    4,012 on 04 Sep 2023 at 17:11:15
    Re: DATEDIFF FUNCTION

    Glad to see I'm not the only one who reponds in blog posts 😁

  • Chris-D Profile Picture
    1,246 on 04 Sep 2023 at 16:02:19
    Re: DATEDIFF FUNCTION

    @RusselThomas 

    "but unless someone has a better idea, it's the best I can come up with."

    Challenge accepted 🙂

     

    @jk123455 

    The DateDiff function is not flawed. The Gregorian calendar (or, the Julian calendar from which it originated) is flawed. DateDiff returns days by default since this is (still not quite 100% accurate) close enough that we can perform calculations based upon it.

     

    The Problem With Dates

    People, and computers, rely on units of fixed size to be able to perform accurate calculations. Hence, we use centimetres, metres, kilometres and not inches, feet, yards and a host of other weirdly defined length measurements.

     

    Dates and times are really, really hard to calculate. These statements are NOT TRUE:

    1. Years have 365 days.
    2. Leap years are every 4 years.
    3. A year is 365.25 days.
    4. Days are always the same length.
    5. Days always have 24 hours.
    6. Minutes always have 60 seconds.
    7. Time zones are constant.

     

    You see, when you get down to accuracy, there’s a ton of stuff that makes this very hard.

     

    The Problem With Months

    As stated already in this thread, months are not of fixed length so we can’t accurately calculate them but we can make presentations that match what humans are expecting to see.

     

    TL;DR

    This code I believe produces what humans are expecting to see. The formula is based on a month’s anniversary – i.e. 10th April to 10th May is one month – rather than trying to approximate the number of days in a month.

     

    The code is more verbose than it needs to be, and explained in the comments. Hopefully this makes it clear to understand. Read on after the code for further information.

     

     

     

    //-- DATES IN EXAMPLES ARE IN UK DD/MM/YYYY FORMAT --//
    
    With({
     // Input section
     // We need a reference date and a date to compare
     // Coalesce will default us to Today(), to prevent blank errors
     RefDate: Coalesce( ReferenceDate.SelectedDate, Today()),
     CmpDate: Coalesce( CompareDate.SelectedDate, Today()),
    
     // Include last day? If this is true, an extra day is added
     // e.g. 01/08/2023 to 01/09/2023 would normally be 1 month. 
     // If we're including the last day, this is 1 month, 1 day
     IncludeLastDay: false
    }, 
     With({
     // The calculation must be based on knowing one date is the past, one is the future
     // Date 1 is the past, Date 2 is the future
     Date1: Min( CmpDate, RefDate),
     Date2: Max( CmpDate, RefDate),
    
     // Useful later to know the orientation of the original dates
     IsPast: RefDate > CmpDate
     }, 
     With({
     // Work out year difference
     YearDiff: Year( Date2) -Year( Date1)
     },
     With({
     // Convert to years BUT...
     // Test first. If date 1 is now in the future, deduct one year
     // e.g. 01/12/2023 to 01/01/2024
     // This would result in a difference of 1 year, but in fact it's only a month
     // Adding a year, we see 01/12/2024 is now in the future, we deduct one year.
     Years: YearDiff -If( DateAdd( Date1, YearDiff, TimeUnit.Years) > Date2, 1)
     },
     With({
     // Move Date1 forward by years to calculate remaining months
     Date1: DateAdd( Date1, Years, TimeUnit.Years)
     },
     With({
     // To work out months, we make a list of the 11 months preceeding Date 2
     // Then count how many of them are in the future compared to Date 1
     Months: CountIf( 
     ForAll( Sequence(11), DateAdd( Date2, -Value, TimeUnit.Months)),
     Value >= Date1
     )
     },
     With({
     // We need to do something different for longer months
     // e.g. 31st May cannot be rolled back to 31st April. 
     // April only has 30 days so this causes an anomaly
     // Test this by changing the date and checking if the Day() component is different
     LongerMonth: Day( DateAdd( Date2, -Months, TimeUnit.Months)) <> Day( Date2)
     },
     With({
     // We need to adjust a date to work out the remaining days
     // Depending on if it's a longer month, there are 2 ways we do this
     // If it's a longer month, bring the past date forward
     // Else bring the future date backwards
     Date1: If( LongerMonth, DateAdd( Date1, Months, TimeUnit.Months), Date1),
     Date2: If( LongerMonth, Date2, DateAdd( Date2, -Months, TimeUnit.Months))
     },
     With({
     // Work out the remaining day difference, adding one extra if IncludeLastDay
     DayDiff: DateDiff( Date1, Date2) +If( IncludeLastDay, 1)
     },
     With({
     // Weeks are 7 days, obviously
     Weeks: RoundDown( DayDiff /7, 0)
     },
     With({
     // Remaining days
     Days: DayDiff -Weeks*7
     },
     With({
     // Make a list of strings to form the output
     // Put each unit, Years, Months, Weeks, Days into a separate string
     // Test for 0's, if a unit is zero the output is blank
     Segments: [
     If( Years, $"{Years} year{If( Years <> 1, "s")}"),
     If( Months, $"{Months} month{If( Months <> 1, "s")}"),
     If( Weeks, $"{Weeks} week{If( Weeks <> 1, "s")}"),
     If( Days, $"{Days} day{If( Days <> 1, "s")}"),
    
     // This is a default in case the 2 dates are the same
     If( !Years && !Months && !DayDiff, "0 days")
     ]
     },
     With({
     // Filter out the blanks
     Segments: Filter( Segments, !IsBlank( Value))
     },
     // Use substitute to make the last part have 'and' instead of ','
     Substitute(
     // Format string and add some other information
     Concat( Segments, Value, ", ")
     & If( IsPast, " since ", " until ")
     & CmpDate,
     ", ",
     " and ",
     Max( CountRows( Segments) -1, 1)
     )
     )
     )
     )
     )
     )
     )
     )
     )
     )
     )
     )
     )
    )

     

     

     

    Further Reading

    I originally did this in a button using context variables. The code is nicer that way but I think most people will only want this output in a text field so there’s a bunch of stacked With()’s.

     

    Function Reference

    With() 

    DateAdd(), DateDiff()

     

    Stacking With() and Scope

    I didn’t really understand With from the documentation so here’s my explanation.

     

    With() works like this:

     

     

    With({
     … define some variables here …
    },
     … do some code here …
    )

     

     

    You can perform your calculation steps when you define variables.

    Any variable defined will be available downstream but not upstream.

    Any variable defined with the same name replaces the previous definition.

     

    Example:

     

     

    Set( MyVar, 3);
    
    With({
     Name: "Chris",
     Age: 20,
     Foo: "Bar"
    },
     // MyVar = 3, Name = Chris, Age = 20, Foo = Bar
     With({
     Age: Age +1
     },
     // MyVar = 3, Name = Chris, Age = 21, Foo = Bar
    
     With({
     Name: "John",
     Age: Age +1
     },
     // MayVar = 3, Name = John, Age = 22, Foo = Bar
    
     $"My name is {Name}, I'm {Age} and Foo is {Foo}"
     )
     )
    )

     

     

     

     Date Calculations

    First we resolve our 2 dates into past and future. Without this our durations would come out negative and we’d need more if statements for some of the calculations. It’s just easier to set the dates up first.


    I’ll describe each step using dates rather than variable names for clarity.

     

    Years

    25/12/2019 compared to 21/06/2023.


    This one is quite easy.


    2023 – 2019 is 4 years... sometimes.


    25/12/2019 to 01/01/2023 would result in 4 but is only 3 full years so we check this by adding 4 years to 2019 and see if the date is still in the past.


    01/12/2019 + 4 years = 01/12/2023 which is after 01/01/2023 so we deduct one year to account for this.

     

    Months

    25/12/2022 compared to 21/06/2023.


    We make a list of the 11 months prior to 21/06/2023, not including the current month.


    21/05/2023
    21/04/2023
    21/03/2023
    … etc …

     

    Then count how many of those months are still in the future compared to 25/12/2023.

     

    That’s January through to May, 5 months. But wait!


    This is where the human expectation throws a spanner in the works.


    Consider this.


    28/02/2023 to 01/03/2023. This is one day, right?


    28/02/2023 to 31/03/2023. This one is open to interpretation. If I said to you, 10th April to 10th May, I think it’s fair to say that everyone would agree this is one month. In this case, 28th Feb to 28th March would be one month. If that’s true then 28th Feb to 31st March should be 1 month, 3 days.


    So then, 28/02/2023 to 01/04/2023. Hmmm. Now we have a problem. This is one day after 31/03 so should it be 1 month, 4 days? The maths part of me says it is but the human says ‘No, wait? What?’ 01/03 is one day. 01/04 should be 1 month, 1 day.


    This is where we cannot solve the problem. There is no right/wrong answer, only what people expect.


    If you are rigid in your maths and demand that we stick with 1 month, 1 day, then consider this. How do you add one month to 31st March?


    So I added a variation here to satisfy the human in me.


    In this case, instead of moving the past date forward 1 month (28/03/2023 gives 4 days until 01/04), we move the future date backwards. Now, 01/04/2023 becomes 01/03/2023. We still get 1 month, but now the remaining difference is only 1 day.


    It does create the oddity that we seem to lose a day or two here and there, but I believe is as accurate as humans would expect it to be.


    We can use method 1 here so 25/12/2022 + 5 months = 25/05/2023

     

    Days

    25/05/2022 to 21/06/2023


    Now it’s just a simple matter of comparing the days remaining and converting to weeks and a remainder. No tricks needed here.


    27 days difference = 3 weeks and 6 days.

     

    Output

    I use a list here. Defining a variable this way is effectively the same as a collection, although there are some functions you can’t perform on a list defined in this way.

     

    My list will look like this:
    3 years
    5 months
    3 weeks
    5 days

     

    If one of these was zero we’d get a blank which causes a problem when we try and put these together. The next step is to run a Filter() on this to get rid of the blanks.


    Then we use a Concat to join whatever is remaining with commas and a Substitute() to change the last comma to ‘and’, just to correct our grammar 🙂

     

    Testing

    I’ve tested this against known dates, odd dates and it always seems to work. I’ve also compared the output to Russel’s solutions. Russel’s solutions are less accurate over longer periods of time.


    I also took the liberty of ‘fixing’ Russel’s first solution. The months didn’t seem to account for the counted years so I added a fix for this, although this causes it to count backwards for dates less than a year apart.


    Time since…..

     

    My example described above. Christmas 2019 to Summer Solstice 2023… for no particular reason!

    ChrisD_0-1693843159391.png

     

    Last Christmas

    ChrisD_1-1693843197091.png

     

    Date oddity, as described above. 3 days vanished!

    ChrisD_2-1693843216919.png

    Since the turn of the millennium

    ChrisD_3-1693843277739.png

     

    Since the moon landing. The first one. Yeah, I know, but Power Apps doesn’t predate 01/01/1970. Incidentally, in code time, this date is 0.

    ChrisD_4-1693843314894.png

     

    I've spent far too long on this, but it was fun! 😂

    -------

    If you liked this post, show some love and give some thumbs 😉

  • Suchithra_A Profile Picture
    88 on 06 Apr 2023 at 12:34:12
    Re: DATEDIFF FUNCTION

    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

     

  • jk123455 Profile Picture
    6 on 15 Jul 2022 at 03:56:24
    Re: DATEDIFF FUNCTION

    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.

  • jk123455 Profile Picture
    6 on 15 Jul 2022 at 03:54:49
    Re: DATEDIFF FUNCTION

    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.

  • Verified answer
    RusselThomas Profile Picture
    4,012 on 24 Nov 2020 at 16:23:24
    Re: DATEDIFF FUNCTION

    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

  • Verified answer
    RusselThomas Profile Picture
    4,012 on 23 Nov 2020 at 19:36:37
    Re: DATEDIFF FUNCTION

    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

  • DHAGZ Profile Picture
    162 on 23 Nov 2020 at 06:45:26
    Re: DATEDIFF FUNCTION

    🙂 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 Profile Picture
    4,012 on 23 Nov 2020 at 05:59:16
    Re: DATEDIFF FUNCTION

    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 Profile Picture
    162 on 23 Nov 2020 at 05:31:15
    Re: DATEDIFF FUNCTION

    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


Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Understanding Microsoft Agents - Introductory Session

Confused about how agents work across the Microsoft ecosystem? Register today!

Warren Belz – Community Spotlight

We are honored to recognize Warren Belz as our May 2025 Community…

Kudos to the March Top 10 Community Stars!

Thanks for all your good work in the Community!

Leaderboard

#1
WarrenBelz Profile Picture

WarrenBelz 146,731 Most Valuable Professional

#2
RandyHayes Profile Picture

RandyHayes 76,287 Super User 2024 Season 1

#3
Pstork1 Profile Picture

Pstork1 66,075 Most Valuable Professional

Leaderboard