cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
JordanBowman
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

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

7 REPLIES 7
ScottShearer
Super User III
Super User III

@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

@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

@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

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

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

Helpful resources

Announcements
Process Advisor

Introducing Process Advisor

Check out the new Process Advisor community forum board!

MPA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Top Solution Authors
Users online (18,620)