cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Excel datetimes are in float

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

1 ACCEPTED SOLUTION

Accepted Solutions

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

 

View solution in original post

5 REPLIES 5
Mira_Ghaly
Dual Super User II
Dual Super User II

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

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.

Blog: here
manuelstgomes
Super User
Super User

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
Not applicable

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. 

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

 

View solution in original post

Anonymous
Not applicable

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

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Users online (1,160)