I logged a suggestion in Connect long time back, about lack of DATEDIFF function option in calculated field (https://connect.microsoft.com/dynamicssuggestions/Feedback/Details/1086828).
Quite to my surprise, CRMOnline Update 1 (Carina) now has a bunch of new DATEDIFF functions. These are
https://youtu.be/NJgRctOncuA?t=855 "NOW" returns SQL Server UTC Time and not the user's local time. This is true, only if the datetime field is Time Zone Independent. If the datetime field is created as User Local, "NOW" returns user's local timezone.
If you try to use NOW function in a datetime field with behaviour Time Zone Independent, you'll get "You can only use a Time-Zone Independent Date Time type field" error message.
The trick to getting UTC time in this case, is to first create the datetime field as User Local, fill in the calculation field action, and only then change the datetime behaviour to Time Zone Independent. Also note that, it is possible to change the datetime behavior from User Local to Time Zone Independent, but not the other way around (from the UI).
I created two calculated fields using NOW function, one is User Local and one is Time Zone Independent. After doing an Advanced Find with the attributes, here is the result.
DIFFINYEARS and NOW in combination, can be used in scenarios like calculating Age from Date of Birth, days since record creation, days to hit a certain deadline. These calculations, which were once accomplished by a running a periodic workflow or service, are trivial to implement with calculated fields. For eg. age calculation
This is the Advanced Find result
These great additions in the Spring Update, make calculated fields more powerful than ever.
Quite to my surprise, CRMOnline Update 1 (Carina) now has a bunch of new DATEDIFF functions. These are
- DIFFINDAYS
- DIFFINHOURS
- DIFFINMINUTES
- DIFFINMONTHS
- DIFFINWEEKS
- DIFFINYEARS
https://youtu.be/NJgRctOncuA?t=855 "NOW" returns SQL Server UTC Time and not the user's local time. This is true, only if the datetime field is Time Zone Independent. If the datetime field is created as User Local, "NOW" returns user's local timezone.
If you try to use NOW function in a datetime field with behaviour Time Zone Independent, you'll get "You can only use a Time-Zone Independent Date Time type field" error message.
I created two calculated fields using NOW function, one is User Local and one is Time Zone Independent. After doing an Advanced Find with the attributes, here is the result.
DIFFINYEARS and NOW in combination, can be used in scenarios like calculating Age from Date of Birth, days since record creation, days to hit a certain deadline. These calculations, which were once accomplished by a running a periodic workflow or service, are trivial to implement with calculated fields. For eg. age calculation
These great additions in the Spring Update, make calculated fields more powerful than ever.
Thanks!!
ReplyDeleteI notice that it shows the ones with DOB in December 2013 as age 2, when really, they are age 1 until December 5, 2015. I may have to use DIFFINMONTHS / 12 to get a more accurate age.
ReplyDeleteOh sorry - my American bias is showing -- I made the (apparently incorrect) assumption that 12/05 is December 5, rather than May 12.
ReplyDeleteShould we assume that if you had a birthdate of 27July2010 that the DIFFINYEARS would be 4 (up until 27July2015)?
I tried using the example and it seems DIFFINYEARS consider only the Year part of the date, that means if current date is 25 May 2015, DIFFINYEARS(01 June 2016, Now()) returns 1
ReplyDeleteI'm having a serious problem with the 2015 Spring update. I work in PST and the rest of my office works EST. This led to the annoying behavior that clients' birthdays showed up differently for me than the rest of our users. With the addition of "Behavior: Date Only" in the new update I was very happy. No more changing birthdays based on what time zone I lived in. HOWEVER, now I have the issue that I cannot calculate clients' ages from the DIFFINYEARS function, i.e., DIFFINYEARS(birthdate,NOW()).
ReplyDeleteIs my only solution really to create A SECOND birthday field set to the local user's timezone?