• Yash Agarwal

Working with Date & Time data of Excel in Power Automate

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!


SUBSCRIBE VIA EMAIL

Yash Agarwal

  • Black LinkedIn Icon
  • Black Twitter Icon
  • Black Facebook Icon
  • Black Instagram Icon
  • Black RSS Icon

© 2019 By the Devs