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:
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
Solved! Go to 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
I believe the following format string will work for you:
dd-MM-yyyy HH:mm:ss
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
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.
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
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
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
Life saver! I had the same issue and no one was considering the time aspect in their solutions, thank you!
thank you!! life saver indeeed!