UPDATED as of 17-03-2021
You can now get a properly formatted ISO 8601 formatted date from excel by selecting it on the option as shown in the image below. In case you still want to proceed with the number format, you can check the post further.
In this #PowerShot I will show you how to work with Date & Time data type of Excel tables in Power Automate. Currently we see that the Date and Time values from Excel are returned as numbers in Power Automate. In this post we will look at a couple of methods to resolve this.
Let's get Started!
The Date and Time values from excel are returned as numbers in Power Automate when the column datatype is formatted as a custom or a date time type in excel. Here are the fixes that can be applied to address this.
Column formatting in Excel
Simply select the entire column in Excel and change the datatype to general text format. This sends the data in a string format to Power Automate and you can use the 'formatDateTime()' function and build an expression to perform the further actions. Similarly if the column is of text format
Using expressions in Power Automate
Use expressions to convert the numbers to appropriate date time strings. To get deeper into this, we first need to understand the conversion here and then apply the expressions to format the data appropriately.
Most of us know that the number returned for the date can be converted by adding that number to the date 1899-12-30 using the 'addDays()' function.
Now, for the data for time is returned with a number in float format as '0.xxxx'. This basically multiplied by 100 is the percentage of day that has been completed from 00:00:00AM. And you can use the expression below to convert it to the closest time stamp.
Expression: formatDateTime(addMinutes(concat(formatDateTime(utcNow(),'yyyy-MM-ddT'),'00:00:00'),int(mul(variables('code'),1440))),'hh:mm')
This was all about retrieving the values. Now, if you want to write to an Excel, either the column formatting needs to be plain text to simply pass the date and time stamps but if not, you need to again convert the date/ time to the appropriate numbers and send to the excel column so that it can be formatted appropriately.
For a date value:
div(sub(ticks(variables('dateToConvert')),ticks('1899-12-30T00:00:00')),864000000000)
Here, "variables('dateToConvert')" needs to be provided as a date time string in ISO 8601 format.
For a time value:
Expression: div(sub(ticks(concat(formatDateTime(utcNow(),'yyyy-MM-ddT'),variables('timetoconvert'),':00')),ticks(concat(formatDateTime(utcNow(),'yyyy-MM-ddT'),'00:00:00'))),float(864000000000))
Here, "variables('timetoconvert')" needs to be provided as a string in hh:mm format.
In this post, we looked into handling Date and Time type data from Excel tables in Power Automate. Usually when the column datatype is formatted in a custom type or of type date and time, the data is sent as numbers to Power Automate and the above fixes can be applied to get the appropriate data in the required format.
This post was helpful in deriving some of the formulas above.
I hope you found this interesting and it helped you!