Answered

Format datetime from Excel float number (e.g. 45123.4567) in a Data-Select Statement to formatted date (yyyy-MM-dd HH:mm:ss)

(0) Share

Report

Posted on by skillsy

Hi,

I have a Sharepoint based Excel spreadsheet called "last 24" and I want to export these to a csv for use in another system. The dateLogged field is an Excel date/time column but Flow is seeing this as a string. I am trying in the "data/Select" (non-system fields)" to convert this to a date (better still a date/time) but int() and float() are not converting this to a number.

In testing...

item()?['dateLogged'] returns the string '44152.270860556'
int(item()?['dateLogged']) fails
float(item()?['dateLogged']) returns the float of 44152.270860556 however the addDays function requires an integer and int(float(item()?['dateLogged']))) fails

What is the best way to turn all these dateLogged entries into a standard yyyy-MM-dd HH:mm:ss format within a select function please?

Categories:

General topics

I have the same question (0)

All responses (2)

Answers (1)

Verified answer

yashag2255 24,769 Super User 2024 Season 1 on at

Like (4)

Report

Hi @skillsy

I have a blog post explaining the workaround for this and what expressions can be used to appropriately convert the float to Date and Time: https://www.bythedevs.com/post/working-with-date-time-data-of-excel-in-power-automate

Hope this Helps!

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

Was this reply helpful? Yes No
javierdavidh 9 on at

Like (0)

Report

Hi I found this post and I am havinmg the same issue, so I decided to write directly to the expert, searching for some help please, Hi @yashag2255 , I have follow your post https://www.bythedevs.com/post/working-with-date-time-data-of-excel-in-power-automate , but still not able to get the Date time right,,,
Here is my excel table:

In the flow I read all rows with a condition and want to extract the dates from the columns above and sent an email aith the info, I am able to get the columns " Date Courriel Suivi " and " Date courriel RAPPEL" in the right format but the first and the second one are Always wrong.

I have checked the option format ISO 8601:
I created this compose action juste to check what is coming from the excel file, I am getting this:
Bizarre, but as I explained before but I am getting juste 2 right dates.

So Now I am trying to use your expression to convert the string 45385.6447685185 into the desired format
03/04/2024 15:28

I am able to get the Date but not the time, with the formula I always get 12:00

What I am doing wrong ? I am stuck for long time...

Can I have some help please,

Was this reply helpful? Yes No