Can't use Now() in a calculated field with "Date Only" and "Timezone Independent" behaviors

(0) Share

Report

Posted on by DaveDaveDave

I have 2 fields:

- Start Date (Date Only, behavior = User Local)

- End Date (Date Only, behavior = User Local)

Requirements:

1 - (Elapsed Days) Show the # of days between these 2 dates (if the End Date is empty, show # of days between the Start Date and today's date)

2 - (Fiscal Year) Populate a Fiscal Year text field based on the Start Date

For example: Mar 31, 2021 = "FY 2020-21", April 01, 2021 = "FY 2021-22"

For item 1, I easily created a calculated field using this formula when the End Date is empty:

- DIFFINDAYS(my_startdate, Now())

For item 2, I have JavaScript on my Main Form to populate the Fiscal Year when the Start Date changes, but since the behavior for my field is "User Local", and I am in the EST time zone, the actual date that gets returned by getValue() is 4 hours before midnight, therefore when the user enters "April 01", the JavaScript thinks it is "Mar 31 at 8:00PM".

If I change my Start Date field's behavior to "Date Only" or "Timezone Independent", the JavaScript works perfectly, but now I can't use the NOW() function in the Calculated field!!!

It seems that I am stuck in a catch-22 situation...

I have done many searches, and I can see others have reported this issue, but I cannot find a solution. I have found posts that suggest creating a hidden calculated field on my table to store today's date using the Now() function, but in order for this hidden field to be used in the DIFFINDAYS function instead of using NOW(), it would also need to have Behavior = Date Only or Timezone Independent, and it won't accept Now() as the value for the calculation.

I hope I have explained this well.

Why is there no "TODAY()" function that would simply return a Date Only value?? There is only a NOW() function that cannot be used with "Date Only" and "Timezone Independent" behaviors.

Categories:

Microsoft Dataverse with Power Apps

I have the same question (0)

All responses (2)

Answers (0)

Drew Poggemann 9,287 Most Valuable Professional on at

Like (1)

Report

Hi @DaveStorey ,

What about using a realtime workflow that runs as the user that made changes to the record, as this would run in the same timezone as that user, and if they enter April 1st as the start date you can set the fiscal year based on this. It might require a custom workflow action to do your logic to set the fiscal year as my example below is very hard-coded to the specific year.

Another advantage of this type of approach is if the data changes through a import / export or other approach the workflow would be called and the date adjusted where if you have JavaScript on the form it would not be called in that situation. Of course a plug-in is another option...

Hope this helps. Please accept if answers your question or Like if helps in any way.
Thanks,
Drew

Was this reply helpful? Yes No
MayankP 464 on at

Like (1)

Report

Hi,

I guess in Javascript you could use following code/function to get correct local date..

function getLocalDate(curdate) {
var dt = new Date(curdate);
var minutes = dt.getTimezoneOffset();
dt = new Date(dt.getTime() + minutes*60000);
return dt;
}
//We can call it like this
var localdateObj = getLocalDate('2015-09-25T02:57:46');

see if this helps ...

Was this reply helpful? Yes No