Answered

DiffInDays() not working in calculated field when date field has "Date Only" behavior

(0) Share

Report

Posted on by tschopp

311

In my model-driven app dataverse, I have a Date Only field (Birth Date) that was using the behavior of "User Local". That field was also accessed in the calculated field formula for another field:

DiffInDays(spear_birthdate, Now())

Now I have to change the Birth Date behavior type to "Date Only" to avoid timezone issues. But that breaks the DiffInDays calculated logic. How can I change that formula to work again? Is there a way to "cast" Birth Date as a date/time value for the formula?

Categories:

Microsoft Dataverse with Power Apps

I have the same question (0)

All responses (5)

Answers (1)

ChrisPiasecki 6,422 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

Hi @tschopp,

When you change the behavior of a Date/Time column, you'll want to go into any dependant calculated columns, business rules, and workflows and deactivate/reactivate and resave them so they get the latest behavior metadata from your date field.

More info here.

---
Please click Accept as Solution if my post answered your question. This will help others find solutions to similar questions. If you like my post and/or find it helpful, please consider giving it a Thumbs Up.

Was this reply helpful? Yes No
Ram Prakash Duraisamy 5,699 Super User 2026 Season 1 on at

Like (0)

Report
Copy link

Link copied!

Hi @tschopp,

Hope the field is not Timezone independent.

Was this reply helpful? Yes No
tschopp 311 on at

Like (0)

Report
Copy link

Link copied!

@ChrisPiasecki Yes, thank you. That's what I was doing. Except when I open the calculated field formula and try to save it, I get this error:

You can't use Now(), which is of type DateTime, with the current function.

Except I don't think the "Now()" part is the issue. The DiffInDays() function works fine with Now() when the other value is a date only field with the "User Local" behavior set.

Was this reply helpful? Yes No
Verified answer

MayankP 464 on at

Like (0)

Report
Copy link

Link copied!

@tschopp have you looked at this one http://nycrmdev.blogspot.com/2015/05/new-calculated-field-functions-in.html?

Was this reply helpful? Yes No
tschopp 311 on at

Like (0)

Report
Copy link

Link copied!

@MayankP I guess that's how it must be done for now:
"The trick to getting UTC time in this case, is to first create the datetime field as User Local, fill in the calculation field action, and only then change the datetime behaviour to Time Zone Independent."

The same seems to be true for Date-Only fields. BTW, even though I cannot edit and save my existing calculated field formulas without that error, they do seem to be working.

Was this reply helpful? Yes No