Customer Engagement & Dynamics CRM Forum

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

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

Original Message:
Sent: Jan 31, 2020 08:56 AM
From: Eli Rodriguez
Subject: Calculating DiffinDays with a Date Only Field

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

Thanks @Rex Kenley Tan, so without custom code this would not be achievable through OOB calculated field functionality?​

------------------------------
Eli Rodriguez
Systems Engineer
WI
------------------------------

Original Message:
Sent: Jan 31, 2020 03:20 PM
From: Rex Kenley Tan
Subject: Calculating DiffinDays with a Date Only Field

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.

Original Message:
Sent: Jan 31, 2020 08:56 AM
From: Eli Rodriguez
Subject: Calculating DiffinDays with a Date Only Field

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

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

Original Message:
Sent: Jan 31, 2020 04:39 PM
From: Eli Rodriguez
Subject: Calculating DiffinDays with a Date Only Field

Thanks @Rex Kenley Tan, so without custom code this would not be achievable through OOB calculated field functionality?​

------------------------------
Eli Rodriguez
Systems Engineer
WI

Original Message:
Sent: Jan 31, 2020 03:20 PM
From: Rex Kenley Tan
Subject: Calculating DiffinDays with a Date Only Field

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.

Original Message:
Sent: Jan 31, 2020 08:56 AM
From: Eli Rodriguez
Subject: Calculating DiffinDays with a Date Only Field

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

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/
------------------------------

Original Message:
Sent: Jan 31, 2020 08:56 AM
From: Eli Rodriguez
Subject: Calculating DiffinDays with a Date Only Field

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

Ram, 

I am not able to replicate.

Date Field Properties: 


------------------------------
Eli Rodriguez
Systems Engineer
WI
------------------------------

Original Message:
Sent: Feb 03, 2020 04:20 AM
From: Ram Kiran Yarramasu
Subject: Calculating DiffinDays with a Date Only Field

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/

Original Message:
Sent: Jan 31, 2020 08:56 AM
From: Eli Rodriguez
Subject: Calculating DiffinDays with a Date Only Field

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

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

Original Message:
Sent: Feb 03, 2020 09:07 AM
From: Eli Rodriguez
Subject: Calculating DiffinDays with a Date Only Field

Ram,

I am not able to replicate.

Date Field Properties:

Calculated field:

------------------------------
Eli Rodriguez
Systems Engineer
WI

Original Message:
Sent: Feb 03, 2020 04:20 AM
From: Ram Kiran Yarramasu
Subject: Calculating DiffinDays with a Date Only Field

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/

Original Message:
Sent: Jan 31, 2020 08:56 AM
From: Eli Rodriguez
Subject: Calculating DiffinDays with a Date Only Field

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

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

Original Message:
Sent: Feb 03, 2020 09:07 AM
From: Eli Rodriguez
Subject: Calculating DiffinDays with a Date Only Field

Ram,

I am not able to replicate.

Date Field Properties:

Calculated field:

------------------------------
Eli Rodriguez
Systems Engineer
WI

Original Message:
Sent: Feb 03, 2020 04:20 AM
From: Ram Kiran Yarramasu
Subject: Calculating DiffinDays with a Date Only Field

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/

Original Message:
Sent: Jan 31, 2020 08:56 AM
From: Eli Rodriguez
Subject: Calculating DiffinDays with a Date Only Field

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

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.



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

Original Message:
Sent: Feb 03, 2020 09:07 AM
From: Eli Rodriguez
Subject: Calculating DiffinDays with a Date Only Field

Ram,

I am not able to replicate.

Date Field Properties:

Calculated field:

------------------------------
Eli Rodriguez
Systems Engineer
WI

Original Message:
Sent: Feb 03, 2020 04:20 AM
From: Ram Kiran Yarramasu
Subject: Calculating DiffinDays with a Date Only Field

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/

Original Message:
Sent: Jan 31, 2020 08:56 AM
From: Eli Rodriguez
Subject: Calculating DiffinDays with a Date Only Field

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

Hello Rex,

Server version is 9.1.0000.12059

------------------------------
Eli Rodriguez
Systems Engineer
WI
------------------------------

Original Message:
Sent: Feb 03, 2020 04:20 AM
From: Ram Kiran Yarramasu
Subject: Calculating DiffinDays with a Date Only Field

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/

Original Message:
Sent: Jan 31, 2020 08:56 AM
From: Eli Rodriguez
Subject: Calculating DiffinDays with a Date Only Field

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

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

Original Message:
Sent: Feb 03, 2020 10:31 AM
From: Eli Rodriguez
Subject: Calculating DiffinDays with a Date Only Field

Hello Rex,

Server version is 9.1.0000.12059

------------------------------
Eli Rodriguez
Systems Engineer
WI

Original Message:
Sent: Feb 03, 2020 04:20 AM
From: Ram Kiran Yarramasu
Subject: Calculating DiffinDays with a Date Only Field

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/

Original Message:
Sent: Jan 31, 2020 08:56 AM
From: Eli Rodriguez
Subject: Calculating DiffinDays with a Date Only Field

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

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

Original Message:
Sent: Feb 04, 2020 09:49 AM
From: Ulrik Carlsson
Subject: Calculating DiffinDays with a Date Only Field

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

Original Message:
Sent: Feb 03, 2020 10:31 AM
From: Eli Rodriguez
Subject: Calculating DiffinDays with a Date Only Field

Hello Rex,

Server version is 9.1.0000.12059

------------------------------
Eli Rodriguez
Systems Engineer
WI

Original Message:
Sent: Feb 03, 2020 04:20 AM
From: Ram Kiran Yarramasu
Subject: Calculating DiffinDays with a Date Only Field

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/

Original Message:
Sent: Jan 31, 2020 08:56 AM
From: Eli Rodriguez
Subject: Calculating DiffinDays with a Date Only Field

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