Convert number from text to datetime

[Noah100]

Hello Community!

When I add a row to a table using the add a row to a table action, it takes any datetimes I have and turns them into a text format, Ex: 

8/24/2023 7:55:00 PM get turned into 45162.8298611111. Even if I select ISO 8601 as my DateTimeformat, it can not change the text into ISO 8601 format because it does not recognize the text as a valid datetime. 

Is there a way to convert text number back into the original date time of 8/24/2023 7:55:00 PM? Im trying to use update a row and use compose actions to formatDateTime(text number) and then select the outputs of the compose action in the field for that text number cell in my table. 

Thank you,

Noah Strockbine

[wskinnermctc]

What is the problem with the number in Excel 45162.8298611111 being used?

Can you format the number in Excel using the Excel number format?

[trice602]

Hi @Noah100 ,

You can do this in Excel.  If this was helpful, please mark as a solution.

[Noah100]

@trice602 @wskinnermctc 

This needs to be automated. This is for a log of completed requests and the row it is adding is the average amount of time it takes to complete each request from other tables within the same workbook. When it takes the datetimes in the add a row action, it converts it into text and it needs to turn it back into a datetime within the same flow.

[Noah100]

@trice602 @wskinnermctc 

This is what is in my excel table

The number in Column1 is what it converts it into once it is in power automate

[trice602]

Hi @Noah100 ,

If you can live without the time value, here's one way to convert the serial number to a date value.

The key here is you have to work with integers.

addDays('1899-12-30',outputs('Compose_2'),'MM/dd/yyyy')

If this helps you get up and running, please mark as a solution.

[wskinnermctc]

Please show the steps of your flow, I'm not understanding where the data is coming from and where the data is going to. 

If you are pulling from Excel into Power Automate?

Excel >>>>>>>>>>>>>>>Power Automate

8/24/2023 7:55:00 PM >>>>45162.8298611111

(And I if the Excel column has blank rows at the top of the table it is going to cause power automate to think it is a text column even if you select ISO 8601. I have a post about it here.)

Or

Power Automate>>>>>>>>Excel?

45162.8298611111>>>>>>>8/24/2023 7:55:00 PM

Which one is it that is causing the issue? 

[Noah100]

@wskinnermctc  

The problem is the first one, Excel to power automate and there are no blank rows.

[Noah100]

@trice602 

That is great, but unfortunately I have to have the time as well.

[wskinnermctc]

Ok, if you look in that post there is a conversion expression:

addSeconds('1899-12-30',int(formatNumber(mul(float(item()?['CSVReportLastUpdate']),86400),'0')))

But that doesn't make sense if there are no blank rows and it is still recognizing the data as text. (Even though there are clearly text and blank rows in your screenshot Column 1, which would cause this problem.)

If you select the dropdown ISO 8601 when getting rows from Excel, it should convert them into full date time instead of Serial.

[Noah100]

@trice602 

Is there no way to create HH:mm:ss out of the second half of the number? Then, in one compose action just take the outputs of both the date and HH:mm:ss actions and place them next to each other. Then the outputs of that one compose action can be used as Date Time

[wskinnermctc]

The conversion expression in my response will result in date and time.

[Noah100]

@wskinnermctc 

These actions from @trice602  worked great for finding the date.

There has to be an easier way to get the HH:mm:ss from spliting the second half of the decimal number. 

Your current solution does not seem to work for me.