Customer Engagement & Dynamics CRM Forum

 View Only
Expand all | Collapse all

Calculating DiffinDays with a Date Only Field

  • 1.  Calculating DiffinDays with a Date Only Field

    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

    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

    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

    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

    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

    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

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

    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

    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
    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
    Posted 18 days ago
    @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
    ------------------------------



If you've found this thread useful, dive deeper into User Group community content by role