DateDiff with units set to years rounds to the nearest whole number (up or down)

[Andy_Tuke]

If you have a list of people with a birthday date field.  You would hope to use a formula such as

DateDiff(PatientDOB,Now(),Years)

The output is always rounded to the nearest whole number of years, so someone aged 13 3/4 will return an age of 14.  You can work around this by returning days and dividing by 365.25 but it would be nice to be able to choose

[droper]

Good suggestion Andy.

I too discovered the annoying rounding up when trying to calculate client age being over 18 (for legal duty of care purposes), and powerapps in its infinate wisdom thinks 17 years and 11 month to be "close enough" to 18 - unfortunatley the law wouldn't agree with that logic!

Your trick of dividing days by 365.25 gives a close enough result for my purpose (within 1 day accuracy) but totally agree that rounding should be an option!

[kostasoul]

Just an FYI, this affects Days as well

https://powerusers.microsoft.com/t5/Building-Power-Apps/DateDiff-Days-Round-Up-Bug/m-p/459909#M13754...

[BenDonahue]

Here is a formula that will work:

Numerand/Divisor - Mod(Numerand, Divisor)/Divisor

So, for your use case:

DateDiff(
  PatientDOB,
  Today(),
  Days
)/365.25 - Mod(
  DateDiff(
    PatientDOB,
    Today(),
    Days
  ),365.25
)/365.25

 This is a first attempt at a solution for someone who wasn't me so, if it works, great! If it doesn't, let me know, and I'll take another crack at it. The concept is sound, as it works for my use case:

DateDiff(
  Date(
    Value(yearDate.SelectedText.Value),
    Value(monthDate.Selected.Value),
    Value(dayDate.Selected.Value)
  ),
  Today(),
  Days
) / 365.25 - Mod(
  DateDiff(
    Date(
      Value(yearDate.SelectedText.Value),
      Value(monthDate.Selected.Value),
      Value(dayDate.Selected.Value)
    ),
    Today(),
    Days
  ),
  365.25
)/365.25

I 'd be happy to give a walk-thorough of the formula, if you like, too; just hit me up.

[kostasoul]

Hi @BenDonahue ,

Did you notice my use of RoundDown in the post I had linked to? 

---

We worked around this bug by using a round down,

RoundDown(DateDiff(timeNow, expiryDate, Hours) / 24,0)

but it took a lot of investigation time.  It appears someone has already raised a similar defect with DateDiff around Years back in 2018 but it doesn't look like it was addressed.

---

For years diff, you should be able to change the parameter in the DateDiff and the divisor like so

RoundDown(DateDiff(timeNow, expiryDate, Months) / 12,0)

I know you've got a working solution already, but if you get the opportunity, please compare it to the above and let me know if you get the same results.

Best regards

Kon

[BenDonahue]

@kostasoul ,
I did not. That elegantly and effectively addresses the issue; kudos.

@Andy_Tuke ,
When it rains, it pours; now you have two workable solutions!