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

Like (0) Share

Report

Posted on 22 Nov 2020 03:13:04 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?

All responses (2)

Answers (1)

javierdavidh 9 on 08 Apr 2024 at 17:34:19

Like (0)

Report

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

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,
Verified answer

yashag2255 24,442 Super User 2024 Season 1 on 23 Nov 2020 at 06:51:13

Like (4)

Report

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

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!