You’re offline. This is a read only version of the page.
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
Glad to see I'm not the only one who reponds in blog posts 😁
"but unless someone has a better idea, it's the best I can come up with."
Challenge accepted 🙂
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.
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.
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.
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.
Share
Report
All responses (
Answers (
//-- 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)
)
)
)
)
)
)
)
)
)
)
)
)
)
)
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.
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}"
)
)
)
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.
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.
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
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.
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 🙂
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!
Last Christmas
Date oddity, as described above. 3 days vanished!
Since the turn of the millennium
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.
I've spent far too long on this, but it was fun! 😂
-------
If you liked this post, show some love and give some thumbs 😉
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
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
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
🙂 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.
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
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
#1
WarrenBelz
250
Most Valuable Professional
#2
MS.Ragavendar
122
#3
stampcoin
108
