Blog

Dataverse - How to fix error - This operation cannot be performed on values of different Date Time Behaviors

If you encounter the error "this operation cannot be performed on values which are of different Date Time Behaviors", this post describes the cause and fix for this error.

The Power Up program includes content on creating formula columns in Dataverse. 

A common error that students encounter is "this operation cannot be performed on values which are of different Date Time Behaviors",

This error can be very confusing, therefore, this post describes the cause and fix of this error.

How to recreate the "this operation cannot be performed on values of different Date Time Behaviors" error

This error typically occurs when creating a formula column to calculate a time duration.

The example exercise in the program describes a table called 'Maintenance Log' with a date column called 'Date Observed'.

The task is to create a 'Reporting Days' formula to calculate the difference in days between 'Date Observed' and today's date using the following formula.

DateDiff('Date Observed', Now(), TimeUnit.Days)

This is where we might encounter this error, as shown in the screenshot beneath.


Why this error occurs

The error is caused by the 'Time Zone adjustment' setting on the 'Date Observed' column. 

The screenshot below shows where to find this property in the 'Advanced options' section.



This setting controls how datetime values are stored and displayed for end-users.
 
  • 'User Local' adjusts the date and time values based on the user's local time zone. We use this setting when we want the date and time to reflect the user's local time zone, such as for meeting schedules or deadlines.
  • 'Time Zone Independent' means that no time zone conversion is applied. The date and time values are displayed exactly as stored, regardless of the user's time zone.

When calling the DateDiff function to calculate a difference in days, the behaviour type of the start and end dates must be identical.

If 'Date Observed' were configured as 'Time Zone Independent', it would be incompatible with the Now() function because Now() returns a result of behaviour type 'user local' behaviour. In this case, the way to resolve this is to call the UTCNow() function instead of Now()

As the screenshot below illustrates, the error disappears when we pass UTCNow() to the DateDiff function. 


DateDiff('Date Observed', UTCNow(), TimeUnit.Days)


Alternate fix to the problem

The alternative fix is to ensure that the source date field is of behaviour type 'Local Local'.

Doing so allows us to pass Now() to the DateDiff function without the error, as shown in the screenshot below.

DateDiff('Date Observed', Now(), TimeUnit.Days)

 

Summary

This post explains the cause of the cause of the error "this operation cannot be performed on values which are of different Date Time Behaviors". When creating a date field, it's important to choose the correct behaviour type that matches how you want the date field to behave. If you need to calculate a date difference based on today's date, you would choose to call DateDiff with Now() or UTCNow(), depending on the behaviour type of your date field.

Previous
Related posts