How to Fix Timestamp Issues Between Exchange and SharePoint in Microsoft Flow

Microsoft Flow is often used so users can consume emails and post their content and attachments into a SharePoint list for processing.

The date and time that an email was received are common metrics that users would like to track in SharePoint lists, and the metadata on the email contains that information. However, when you try to use this field, the date and time that flows into the SharePoint list isn’t the date and time the email was received.

Why Is There an Automatic Timestamp Issue in SharePoint? 

The problem stems from the variation in date formatting.

The date and time format that comes back from Exchange emails will resemble the following: 2019-07-02T17:10:36+00:00.

To compare, the date and time format that will be stored in a SharePoint list defaults in the following format: 2019-07-02T17:10:36.0000000Z.

Due to the difference in formatting, if you simply try to store the date and time from Exchange in a SharePoint list without converting it, you will end up getting completely different times than what you would expect.

How to Fix Timestamp Issues with Microsoft Flow 

The easiest way to solve this problem is to simply convert the time we receive from the email into the SharePoint friendly format. To do so, we can use the “Convert time zone” action in Microsoft Flow.

By default, the time zone that items come back in is the UTC Coordinated Universal Time and then it is converted into the end-users local time zone from that base.

With this information, you can convert the Base Time (the email’s received time) into the “Round-trip date/time pattern”. Our source time zone and destination time zone will remain the same as we do not want the actual timestamp itself to change.

The output from this action can then be used to store the proper timestamp in a SharePoint list item as shown below and from there the issue has been resolved.

If you have any questions with this process, let us know. Also, if you work a lot with Microsoft Flow, check out one of our most popular blogs entitled How Substring Works in Microsoft Flow.