cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
New Member

Converting Date and Time From Excel Table

Hi,

 

I am trying to set up a flow that takes a date and time from excel (e.g 06/07/2020 15:09:54) and then add this to a 'Select' function to produce an HTML table. However when I try and run this the date and time is in the format:

 

44018.4144212963
 

I have seen other posts online to conver the date using the expression:

 

addDays('1899-12-30',int(item()?['Date']),'dd-MM-yyyy')

 

However I can't find any example including the time. Can someone please help me be able to include the date and also time.

 

Hopefully someone can help me out!

 

Many thanks,

 

Jordan

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User
Super User

Re: Converting Date and Time From Excel Table

Since because addDays takes second parameter as Integer you can not use the addDays function to get the value of date as its in Decimal.

 

Try this function. ( i am assuming item()?['Date'] is your variable from Excel)

 

 

 

addseconds('1899-12-30', int(formatNumber(mul(float(item()?['Date']),86400),'0','en-us')),'yyyy-MM-dd HH:mm:ss')

 

 

 

Please click Accept as Solution if it resolved your problem or give it a Thumbs Up if it helped you in anyway this will allow other people to search correct solutions effectively.

Thanks,

Rahber

View solution in original post

6 REPLIES 6
Highlighted
Super User III
Super User III

Re: Converting Date and Time From Excel Table

@JordanBowman 

I believe the following format string will work for you:

dd-MM-yyyy HH:mm:ss

 

 

 

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

Scott
Highlighted
New Member

Re: Converting Date and Time From Excel Table

@ScottShearer 

 

Thank you for your response.

 

I have just tried:

 

addDays('1899-12-30',int(item()?['Start time']),'dd-MM-yyyy HH:mm:ss

 

But I get the error code:

 

"TIME": "@addDays('1899-12-30',int(item()?['Start time']),'dd-MM-yyyy HH:mm:ss')"
}' failed: 'The template language function 'int' was invoked with a parameter that is not valid. The value cannot be converted to the target type.'.

 

Please see below screenshot, this also shows the 'Start time' as 44018.4144212963

 

Hopefully you can help

 

Flow Failed.PNG

Highlighted
Super User III
Super User III

Re: Converting Date and Time From Excel Table

@JordanBowman 

The error message seems to be indicating that you used the int() expression with a number that is not an integer.  Try using float() instead.

 

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

Scott
Highlighted
Super User
Super User

Re: Converting Date and Time From Excel Table

Since because addDays takes second parameter as Integer you can not use the addDays function to get the value of date as its in Decimal.

 

Try this function. ( i am assuming item()?['Date'] is your variable from Excel)

 

 

 

addseconds('1899-12-30', int(formatNumber(mul(float(item()?['Date']),86400),'0','en-us')),'yyyy-MM-dd HH:mm:ss')

 

 

 

Please click Accept as Solution if it resolved your problem or give it a Thumbs Up if it helped you in anyway this will allow other people to search correct solutions effectively.

Thanks,

Rahber

View solution in original post

Highlighted
New Member

Re: Converting Date and Time From Excel Table

@Rahber 

 

Thank you very much for your help, workes perfectly! 🙂

 

If you have a spare minute could you please explain to me how this works:

 

addseconds('1899-12-30', int(formatNumber(mul(float(item()?['Date']),86400),'0','en-us')),'yyyy-MM-dd HH:mm:ss')

 

I'm very new to this and still learning, I think I get most of it apart from this part:

 

86400),'0','en-us'

 

Thanks,

 

Jordan

 

Highlighted
Super User
Super User

Re: Converting Date and Time From Excel Table

Sure :).

60 Minutes x 60 Seconds X 24 hours = 86400

 

So i am multiplying the decimal number i get from excel with 86400 this will get me total number of seconds since 1900-1-1

When i am multiplying there is a possibility that i will get the decimal number again so i am using

formatNumber(Value,'0','en-us') to format the number and get a integer out of it.

 

And then i am simply adding that number of seconds to 1899-12-30



Please click Accept as Solution if it resolved your problem or give it a Thumbs Up if it helped you in anyway this will allow other people to search correct solutions effectively.

Thanks,

Rahber

Helpful resources

Announcements
firstImage

Super User Program Update

Three Super User rank tiers have been launched!

firstImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

firstImage

New & Improved Power Automate Community Cookbook

We've updated and improved the layout and uploading format of the Power Automate Cookbook!

thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

Top Solution Authors
Top Kudoed Authors
Users online (6,246)