Excel datetimes are in float

[Anonymous]

Hi, I have 2 Excel columns with datetime formats (eg. 9/8/2020 3:12:00 PM). When I email the HTML table, it ends up being a float: 44082.6333333333. May I know how to change this? Preferably in dd/mm/yyyy hh:mm:ss (in 24h, no am/pm).

I've seen some posts with the following expression but it does not work for my case as I have hours, mins and seconds. 

addDays('1899-12-30',float(item()?['TEST_DM']),'dd/MM/yyyy HH:mm')

[Mira_Ghaly]

@Anonymous 

Have you checked the below post:

https://powerusers.microsoft.com/t5/General-Power-Automate/Convert-Date-Value-in-Excel-To-Date-Value-in-Power-Automate/td-p/494960

If this post helps you with your problem, please mark your as Accepted solution.

If you like my response, please give it a Thumbs Up.

MG (Naturally Curious)

[manuelstgomes]

HI @Anonymous 

I wrote a step-by-step explanation and solution on how to solve this. Also I created a template that you can import that does the conversion for you:

https://manueltgomes.com/microsoft/powerautomate/convert-excel-number-to-date/

If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

Cheers
Manuel

[Anonymous]

Hi @Mira_Ghaly @manuelstgomes 

Thank you for replying. However, my columns have the timestamp as well. So each cell looks like this "9/8/2020 3:12:00 PM", which results in a float of "44082.6333333333" rather than a 5 digit integer. The addDays function will not work as it is a float and not an integer. 

[manuelstgomes · Accepted solution]

Hi @Anonymous 

The floating part relates to the time. Do you need the time as well? For the time value you can follow the scary formula here:

https://www.bythedevs.com/post/working-with-date-time-data-of-excel-in-power-automate

If not you can use the int to return only the integer part

int(...)

If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

Cheers
Manuel

[Anonymous]

Hi @manuelstgomes Yes, I require the time. I think this is it. Thanks!