DIFFINYEARS does not accept second parameter as date and time

[AngeloA]

Hi,

I've created  a new field, whole number, calculated, and am trying to calculate the age of a person from the default fields using:

DIFFINYEARS(birthdate, NOW())

and I even tried

DIFFINYEARS(NOW(), birthdate)

but the second parameter keeps throwing an exception because it is a date and time field (see below).

Can anyone please suggest what I'm doing wrong?

Thanks in advance!

[Anonymous]

@AngeloA 

I don't use CDS so flying blind, but just reading the docs it sounds like your birthdate Field could either Date only or Date and Time. So have you tried using Today() instead of Now(), eg

DIFFINYEARS(birthdate, Today())

[AngeloA]

Thanks for that @Anonymous, but that doesn't seem to be case. According to this article it might be because one date is local, the other is in UTC, but am not sure how to make them both in the same timezone

https://powerusers.microsoft.com/t5/Power-Apps-Ideas/Calculated-fields-needs-to-be-improved-when-working-with/idi-p/221121

[Anonymous]

@AngeloA 

Yes, it does sound like they are different types even though the are dateTime types ... DateTime types across the entire Power Platform drive me nuts.

I was reading this documentation and it seems you may need the help of a developer to change UTC types programmatically, which sucks.

https://docs.microsoft.com/en-us/powerapps/maker/common-data-service/behavior-format-date-time-field

The last thing I'd try would be to wrap both in the DateTimeValue function and see what happens. Maybe try tagging one of the MS moderators to see if they have a solution?

[AngeloA · Accepted solution]

Great @Anonymous, that gave me food for thoughts and it seems to work by creating a new Date Only field, set its behaviour to Date Only, and make it a calculated field with Now().

Now both birthday and the new field with the current date match behaviour, and I can do the date calculation.

Thanks for brainstorming with me 🙂

[Anonymous]

@AngeloA 

Nice work mate! I had fun trying to work through a solution with you and it made me happy to see you solved it yourself 🙂 

All the best.

[Wendy_Lumsden]

So this post is just for anyone else struggling with this problem and fyi also for @AngeloA and @v-siky-msft

I need to calculate the age (in months) of a donation/allocation to use for graphs & dashboards in a Model Driven App.

It seemed that solution on this post which worked was to change the date to type "Date Only".   The date I was using was Date Only but this did not work for me - see error message below:

Then I created a field called DonationDateTime that was Date&Time and got the same error message:

I then added a calculated column called TodayDateTime using the NOW() function as a Date&Time field and this was accepted.

However, when creating the TodayDateTime I made sure that this field was set to the same Time zone adjustment option as the DonationDateTime  (in my case both needed to be Time Zone Independent) which I think is the reason that the final attempt worked: