Why is the DateTime value from Excel Online in the wrong format?
Issue Description: When using Office 365 Read Range activity to get the data from an Excel Table, the DateTime values might be in the wrong date format or in a different format.
E.g.
07/09/2022 (7 Sep), MM/dd/YYYY format, can be fetched as 09/07/2022 (9 Aug), dd/MM/yyyy format.
or
07/09/2022 will be fetched as 44905.646192 (Double value)
Root Cause:
The Office 365 Activities use Graph API as the underlying service, and for some specific date formats or in case of custom formats, Graph API will convert the DateTime values to a simple value or a Double value if a custom date format such as 'dd/MM/yyyy hh:mm:ss' is used.
Value in Excel Online:
Output in Studio:
Resolution: One of the following approaches can be used:
- Insert a single quote at the beginning of the date/time string, which will force Excel to keep the format as a String.
Screenshot from Studio with a quote at the beginning:
How it looks in Excel (the quote is only visible in the Formula bar, but not in the cell):
This will ensure that Graph API will always read the Date as a String and not as a DateTime value, which will prevent it from converting it to a Double value.
- Convert the Double value to a date using the DateTime.FromOADate() method
-
DateTime.FromOADate(DateTimeVariable)
If the value is fetched as an Object type (as in the screenshot from Root cause) section, the Object type needs to be converted to Double type using the 'Cdbl' method before finally converting to DateTime.
-
DateTime.FromOADate(Cdbl(DateTimeVariable))
As a general note, it is determined how DateTime values are managed across all platforms. The correct DateTime format needs to be used such that it will not impact how Graph API processes it. Avoid using Custom values in Excel Online as they will be converted and impact the workflow.
The above can be applied to both adding data to Excel Online and fetching data from Excel Online.