@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!
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 😉
