web
You’re offline. This is a read only version of the page.
close
Skip to main content
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Customer experience | Sales, Customer Insights,... / Problem with date, com...
Customer experience | Sales, Customer Insights,...
Suggested answer

Problem with date, comparison and calculated field

(0) ShareShare
ReportReport
Posted on by DimaN 55

Hi,

so I ran into a small problem with calculated fields and dates while trying to compare 2 dates with each other.

Explanation:

  • Date 1 (e.g. 29.09.2022) and date 2 (Today's date - calculated field with NOW()).
  • Another calculated field with option set should react as follows.
    • Yes:     if Date1    ==     Date2
    • No:      if Date1     !=     Date2

The problem is that this does not work as intended, because the comparison of the two dates also includes the time.

My question is, how can I manage the comparison of a set date and a calculated field with NOW() without the time?

 

Current:
If the date field of Field-Date1 is set to 09/29/2022 and today's Date2-NOW() is also at 09/29/2022, the option set is still set to No (Date1 not equal to Date2-NOW() ), which is odd.

PS: The idea is to avoid business rules or JS as much as possible

I have the same question (0)
  • Community member Profile Picture
    Community member 2 on at
    Problem with date, comparison and calculated field
    I've been having a similar issue. I want to check if a date field is equal to today using a calculated field to calculate warranty status. For this example, lets say we are trying to calculate a birthday and set a calculated field called Birthday Status. If Birth Day is Today, then the Birthday Status field should be "Birthday". The issue is it is not the case when using NOW() to calculate today. I believe this problem is caused by the NOW() function.  Although Todays Date is a Date-Only field, NOW() includes Time and it stores it in the field behind the scenes. So when using a comparison, Todays Date == Birthday when both dates are set to 7/18/23, Todays date will always be greater (except for 1 second). 
     
    Does anyone know another solution? I don't think using a Calculated field Todays Date is the answer as this method is far to rigid. Could we perhaps use Custom API to set todays date whenever a record is being read? 
     
    Also I want to point out that you can't actually use NOW() to calculate a Timezone Independent field. If you try, you will get an error message. You can't have a Todays Date calculated column as Timezone Independent. You will have to find another way of achieving this.
  • Suggested answer
    Amit Katariya007 Profile Picture
    Amit Katariya007 10,409 Super User 2025 Season 2 on at
    RE: Problem with date, comparison and calculated field

    Hello Dima,

    There are 3 different behavior which are associated with the date field.

    User Local :

    - This is the behavior of all date and time fields in the previous releases.

    - The field values are displayed in the current user’s local time.

    - In Web services (SDK), these values are returned using a common UTC time zone format.

    Date Only :

    - The concept of a time zone isn’t applicable to this behavior. The field values are displayed without the time zone conversion.

    - The time portion of the value is always 12:00AM.

    - The date portion of the value is stored and retrieved as specified in the UI and Web services (SDK).

    Time-Zone Independent :

    - The concept of a time zone isn’t applicable to this behavior. The field values are displayed without the time zone conversion.

    - The date and time values are stored and retrieved as specified in the UI and Web services (SDK).

    When the field behavior is User local then the UTC time(stored by CRM) will be converted to the User local time and date.

    so depending on the user time zone it will be changed on the UI side.

    When a date field is Time zone independent, it will be visible same to all the all users.

    To implement your requirement you can make your field as the Time zone independent/Date only. Note : You can change from User Local to Time-Zone Independent any time, but can’t change from Time-Zone Independent to User Local once saved this way.

    There are some limitations with respective to the date only field Please refer below article.

    learn.microsoft.com/.../behavior-format-date-time-field

    d365demystified.com/.../

    Thank you,

    Amit Katariya

  • Suggested answer
    Abdul Wahab Profile Picture
    Abdul Wahab 12,119 Moderator on at
    RE: Problem with date, comparison and calculated field

    Hi Dima Noscik

    Are they all Time Zone Independent? If not then make them Time-Zone Independent.

    If I answer your question then please mark it as verified.

    Let me know if I can provide you with more details.

    Thanks
    Regards,

    Abdul Wahab
    Power Platform & Customer Engagement Developer/Lead/Solution Architecture/Project Manager
    Direct/WhatsApp:+923323281237
    E-mail: abdulwahabubit@outlook.com
    Skype: abdul.wahabubit
    Linkedin: www.linkedin.com/.../

  • DimaN Profile Picture
    DimaN 55 on at
    RE: Problem with date, comparison and calculated field

    Hi, both fields are already set to Date Only. Still doens't work. It always misses by 1 day.

    This works:

    pastedimage1664454614317v1.png

    But this not:

    pastedimage1664454755500v2.png

  • Suggested answer
    Bipin D365 Profile Picture
    Bipin D365 28,981 Moderator on at
    RE: Problem with date, comparison and calculated field

    Hi,

    Can you please change Date Time behaviour to Date Only for you Date fields

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

News and Announcements

Join experts in person to explore low code and AI agents at the Power Platform Community Conference sponsored by Microsoft
Our Community Code of Conduct has been updated!

Quick Links

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Andrés Arias – Community Spotlight

We are honored to recognize Andrés Arias as our Community Spotlight honoree for…

Congratulations to the August Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > Customer experience | Sales, Customer Insights, CRM

#1
Daniyal Khaleel Profile Picture

Daniyal Khaleel 208

#2
Rishabh Kanaskar Profile Picture

Rishabh Kanaskar 182

#3
Tom_Gioielli Profile Picture

Tom_Gioielli 77 Super User 2025 Season 2

Last 30 days Overall leaderboard

Featured topics

Hide selecting Contact for potential customer when creating quote

Product updates

Dynamics 365 release plans