Calculating DiffinDays with a Date Only Field

Calculating DiffinDays with a Date Only FieldFollow
Eli Rodriguez
Eli RodriguezJan 31, 2020 08:56 AM
I have “Field A” with Date Only type, I want to calculate the number of days between that date and NOW(); …
1. Calculating DiffinDays with a Date Only Field

Eli Rodriguez
Posted Jan 31, 2020 08:56 AM
I have “Field A” with Date Only type, I want to calculate the number of days between that date and NOW(); unfortunately NOW() qualifies as Date and Time data. So the calculated field will bounce back with an error. Looking for ideas on how to tackle this.

——————————
Eli Rodriguez
Systems Engineer
WI
——————————

2. RE: Calculating DiffinDays with a Date Only Field

Rex Kenley Tan
Posted Jan 31, 2020 03:20 PM
Eli

You need to convert your date to milliseconds.
const start = new Date(“1/1/2020”),
now = Date.now(),
diff = now – start.getTime(),
diffDays = diff / 86400000;​

——————————
Rex Kenley Tan, MCSA, MCSD
Tallmadge OH
https://www.youracclaim.com/users/rex-kenley-tan

*Always be CURRENT with JavaScript & C#, NEVER be obsolete.

DISCLAIMER: All views expressed on this site are my own and DO NOT represent the opinions of ANY entity whatsoever with which I have been, am now, or will be affiliated.
——————————

3. RE: Calculating DiffinDays with a Date Only Field

Eli Rodriguez
Posted Jan 31, 2020 04:40 PM
Thanks @Rex Kenley Tan, so without custom code this would not be achievable through OOB calculated field functionality?​

——————————
Eli Rodriguez
Systems Engineer
WI
——————————

4. RE: Calculating DiffinDays with a Date Only Field

Rex Kenley Tan
Posted Jan 31, 2020 08:48 PM
@Eli Rodriguez ​

Yes, unfortunately calculated fields are very limited.
https://docs.microsoft.com/en-us/dynamics365/customerengagement/on-premises/customize/define-calculated-fields

Although I would recommend using a plugin to populate this field. JS is limited to the ui, you want this change to happen even on the back end processes.

Cheers!

——————————
Rex Kenley Tan, MCSA, MCSD
Tallmadge OH
https://www.youracclaim.com/users/rex-kenley-tan

*Always be CURRENT with JavaScript & C#, NEVER be obsolete.

DISCLAIMER: All views expressed on this site are my own and DO NOT represent the opinions of ANY entity whatsoever with which I have been, am now, or will be affiliated.
——————————

5. RE: Calculating DiffinDays with a Date Only Field

Ram Kiran Yarramasu
Posted Feb 03, 2020 04:20 AM
Hi Eli,

I’m using Dynamics 365 online version 9.1.0.12059. I created a calculated field to get the number of dates between the date only field and Now() which is date field and I’m able to get the number of days without any errors. I created a calculated field which is whole number and I have date only fields. In the calculated filed actions I write DiffInDays(date only, Now()). I hope this helps you.

——————————
Ram Kiran Yarramasu
Aha Apps
Glen Allen VA
https://www.linkedin.com/in/ram-yarramasu-252901196/
——————————

6. RE: Calculating DiffinDays with a Date Only Field

Eli Rodriguez
Posted Feb 03, 2020 09:07 AM
Ram,

I am not able to replicate.

Date Field Properties:

Calculated field:

——————————
Eli Rodriguez
Systems Engineer
WI
——————————

7. RE: Calculating DiffinDays with a Date Only Field

Rex Kenley Tan
Posted Feb 03, 2020 10:25 AM
Eli

What is your crm version?

——————————
Rex Kenley Tan, MCSA, MCSD
Tallmadge OH
https://www.youracclaim.com/users/rex-kenley-tan

*Always be CURRENT with JavaScript & C#, NEVER be obsolete.

DISCLAIMER: All views expressed on this site are my own and DO NOT represent the opinions of ANY entity whatsoever with which I have been, am now, or will be affiliated.
——————————

8. RE: Calculating DiffinDays with a Date Only Field

TOP CONTRIBUTOR
Axel Girgensohn
Posted Feb 04, 2020 02:37 AM
Hi Eli
your date field has to be Date and Time when using NOW() as NOW() is now on the second. Try to change your formula from using your date field to use createdon and you will see that the formula validates.

——————————
Axel Girgensohn
Dynamics CRM Specialist
Aller Media AB
——————————

9. RE: Calculating DiffinDays with a Date Only Field

Ram Kiran Yarramasu
Posted Feb 04, 2020 03:25 AM
Edited by Ram Kiran Yarramasu Feb 04, 2020 03:56 AM
Sorry I didn’t see your reply that you mentioned your version earlier. For me it is working fine and I’m getting the value too.

In my case I used behavior as “user local” and format as Date only. It worked for me. Please create a new field as shown below. I guess it will help you fix the issue.

——————————
Ram Kiran Yarramasu
Aha Apps
Glen Allen VA
https://www.linkedin.com/in/ram-yarramasu-252901196/
——————————

10. RE: Calculating DiffinDays with a Date Only Field

Eli Rodriguez
Posted Feb 03, 2020 10:32 AM
Hello Rex,

Server version is 9.1.0000.12059

——————————
Eli Rodriguez
Systems Engineer
WI
——————————

11. RE: Calculating DiffinDays with a Date Only Field

MICROSOFT MVP
Ulrik Carlsson
Posted Feb 04, 2020 09:50 AM
Hi Eli

The NOW() calculation will not work with a field where behavior is Date Only. The NOW() calculation actually uses the time, even when just counting days. So users in different time zones may actually not see the result they are expecting for a number of hours.

As Ram mentions, the Behavior needs to be set to User Local and then the NOW() calculation will work. The reason is that the User Local behavior actually stores the time too, but do not show it to users when Format is Date Only. That’s why NOW() will work for that type, but not the Date Only behavior.

A little confusing since “Date Only” is an option in both the Behavior and Format option.

——————————
Ulrik Carlsson
Sr. Solution Architect (CRM Chart Guy)
eLogic
Petaluma CA
——————————

12. RE: Calculating DiffinDays with a Date Only Field

TOP CONTRIBUTOR
Niels Søgaard Lønberg
Posted Jan 19, 2023 03:47 AM
@Eli Rodriguez did you ever find a solution?

Old thread, I know but I am trying to achieve the same 🙂

I can achieve what I am trying to by using the new Power Fx columns (Preview). However, I need calculate progress between start date, end date and today’s date as a whole number. However, the Power Fx column becomes a decimal number column.

Alternatively, I can use Power Automate – but it just seems like overkill for this purpose.

——————————
Niels Lønberg
Digital Sales Specialist
Semco Maritime
——————————