Configure Dataverse Date and Time Column

Dustin Miner

This post provides an example of how to configure the Dataverse ‘Data and Time’ column. The example describes the issue in Figure 1 (taken from https://learn.microsoft.com/en-us/power-apps/maker/data-platform/behavior-format-date-time-field)

Figure 1

Background

Referring to Figure 2 (the environment is set to GMT+11hrs), the ‘Agreement Date’ (Data type = Date and Time) can be configured differently in Dataverse. For example:

1.) Format = ‘Date only’, Behaviour = ‘Date only’ then the value stored in Dataverse is 15/03/2021 12:00:00 AM

2.) Format = ‘Date only’, Behaviour = ‘User Local’ then the value stored in Dataverse is 14/03/2021 1:00:00 PM (this is how it’s configured in the ‘Issue’ section below)

3.) Format = ‘Date and time’, Behaviour = ‘Time zone independent’ then the value stored in Dataverse is 15/03/2021 8:00:00 AM

4.) Format = ‘Date and time’, Behaviour = ‘User local’ then the value stored in Dataverse is 14/03/2021 9:00:00 PM (this is how it’s configured in the ‘Solution’ section below)

Figure 2

Issue

1.) Within Dynamics 356, specify the format on the Agreement Date column as ‘Date Only’ (Figure 3 & point 2 in the ‘Background’ section)

Figure 3

2.) See the ‘Agreement Date’ value (Figure 4).

Figure 4

3.) When the Agreement Date is stored in Dataverse, it’s stored as GMT+0hrs (Figure 5)

Figure 5

4.) When the Agreement Date is read into Power Automate, it’s incorrectly set to the previous day (Figure 6)

Figure 6

Solution

1.) Set the format for the field as ‘Date and Time’ (Figure 7 & point 4 in the ‘Background’ section).

Figure 7

2.) The time is now also displayed on the form (Figure 8). To hide this, add the following JavaScript to the form on load event

FormContext.getControl(“<publisher>_agreementdate”).setShowTime(false);

Figure 8

3.) When the Agreement Date is read in Power Automate, it now displays the time as well (Figure 9)

Figure 9

4.) The Agreement Date is then converted from GMT+0hrs to GMT+11hrs (1 extra hour for daylight savings) (Figure 10)

Figure 10

5.) The date now correctly displays within Power Automate (Figure 11)

Figure 11

Further reading

Power Automate blog posts

References

https://learn.microsoft.com/en-us/power-automate/convert-time-zone

Post Views: 593

Related Posts