Converting Date and Time From Excel Table

[JordanBowman]

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

[ScottShearer]

@JordanBowman 

I believe the following format string will work for you:

dd-MM-yyyy HH:mm:ss

[JordanBowman]

@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

[ScottShearer]

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

[Rahber · Accepted solution]

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

[JordanBowman]

@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

[Rahber]

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

[Joseph_PA]

Life saver! I had the same issue and no one was considering the time aspect in their solutions, thank you!

[jtanwm]

thank you!! life saver indeeed!