cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
AngeloA
Helper III
Helper III

DIFFINYEARS does not accept second parameter as date and time

DIFFINYEARS_Error.png

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!

 
1 ACCEPTED SOLUTION

Accepted Solutions

Great @Eelman, 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 🙂

View solution in original post

5 REPLIES 5
Eelman
Community Champion
Community Champion

@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())

 

Thanks for that @Eelman, 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-wor...
Eelman
Community Champion
Community Champion

@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?

 

 

Great @Eelman, 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 🙂
Eelman
Community Champion
Community Champion

@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.

Helpful resources

Announcements
Ignite 2022

WHAT’S NEXT AT MICROSOFT IGNITE 2022

Explore the latest innovations, learn from product experts and partners, level up your skillset, and create connections from around the world.

Power Apps Africa Challenge 2022

Power Apps Africa Challenge

Your chance to join an engaging competition of Power Platform enthusiasts.

Top Kudoed Authors
Users online (3,614)