Unanswered

Convert number from text to datetime

(1) Share

Report

Posted on by 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

Categories:

General topics

I have the same question (0)

All responses (12)

Answers (0)

wskinnermctc 6,519 Moderator on at

Like (1)

Report

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?

Was this reply helpful? Yes No
trice602 15,402 Super User 2025 Season 2 on at

Like (2)

Report

Hi @Noah100 ,

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

Was this reply helpful? Yes No
Noah100 on at

Like (0)

Report

@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.

Was this reply helpful? Yes No
Noah100 on at

Like (1)

Report

@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

Was this reply helpful? Yes No
trice602 15,402 Super User 2025 Season 2 on at

Like (1)

Report

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.

Was this reply helpful? Yes No
wskinnermctc 6,519 Moderator on at

Like (0)

Report

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?

Was this reply helpful? Yes No
Noah100 on at

Like (0)

Report

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

Was this reply helpful? Yes No
Noah100 on at

Like (0)

Report

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

Was this reply helpful? Yes No
wskinnermctc 6,519 Moderator on at

Like (1)

Report
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.

Was this reply helpful? Yes No
Noah100 on at

Like (0)

Report

@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

Was this reply helpful? Yes No