Addressing Common Challenges with Dates in Dynamics 365

Defaulting Date to Today()

Common issue: "How can I default a date on a form (i.e. a submit date) to today's date?"

Use Real-Time Workflow

Implement a real-time workflow that fires on the creation of the entity. The workflow will set a date field on the entity to the process should pull the dynamic value of the process date and set a date field on the entity. When the record is saved the field will be populated.

One challenge of this approach is the field is not populated until the record is saved.

Use Custom Code

Another way to set a field on the form and have it populated on initial form load is through a JavaScript resource that will fire on the OnLoad event of the form. The JavaScript function would be fairly simple and straight-forward.

Xrm.Page.getAttribute("new_currentdate").setValue(Date.now());

Remember, if you only want to set this if blank, you will want to wrap this with an if statement so it is not reset every time the form is opened...

if (Xrm.Page.getAttribute("new_currentdate").getValue() !== null) { 
   Xrm.Page.getAttribute("new_currentdate").setValue(Date.now());}

Calculating Age

Common Issue: How do I calculate the age of someone in Dynamics 365?
Calculating age in Dynamics 365 has been something I have seen in a number of posts over the years. Overall, the general approach appears to calculate the days between two dates and then dividing this by 365 to get the age in years. Example, you could use a calculated field that will have a formula like:

DIFFINDAYS(new_DOB, NOW()) / 365>

Now, this will work in many situations but it will not work correctly with handling leap years so if you are born anywhere close to February 28th this will probably not provide a correct answer. Leap years are tricky: A leap year is every 4 years, but not every 100 years, then again every 400 years. To address this leap year challenge, I did the following:

1. Created a birthday field that was date and time, this is required for the calculations, date only did not work.
2. Created an Age field that was a Decimal format with 7 decimal places to address rounding because some of the values get very close and you don't want it to round up or down.

Calculation in Age calculated field:

(DiffInDays(new_birthday, now()) - (DiffInYears(new_birthday, now()) / 4)) / 365

This calculation will work for anyone born after the year 1900 and it will work through the year 2099 as the divide by 4 will not work after that date :) If you are trying to future-proof the past that then you will need to add more to the calculation but I expect this will work for most situations.

Dates in Workflow

Many times I find myself needing to manipulate dates in workflows. This could be setting a follow-up date, calculating how many days something has been open, adding a number of business days to another date value, etc. The best tool I have found for this and utilize all the time is MVP Jason Lattimer's date and time utilities. This toolset has helped me numerous times to accomplish date manipulation in workflows that I would not have been able to accomplish without custom workflow activities otherwise.

SSRS Date Example

Returning records in date ranges in SSRS reports is a common reporting type of need in the forums, especially when it comes to a date range compared to the current date. The following is an example response I provided to a question around returning the past 12 months of records related to a specific date on an entity. Hopefully this is helpful for anyone with a similar challenge.

ah.accountdate >= DATEADD(month, -12, DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)) 
AND ah.accountdate < DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)

Hope this is valuable. Thanks much!
-Drew