Answered

Excel datetimes are in float

(1) Share

Report

Posted on by Community Power Pla... Microsoft Employee

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')

Categories:

General topics

All responses (5)

Answers (1)

Sort by

Mira Ghaly 11,415 Moderator on at

Like
a
(0)

Report
Copy link

Link copied!

@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 6,643 on at

Like
a
(0)

Report
Copy link

Link copied!

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
Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

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

manuelstgomes 6,643 on at

Like
a
(0)

Report
Copy link

Link copied!
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
Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

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