cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
rhaddad87
Frequent Visitor

Convert 4 strings to 2 dates with times and calculate time difference between the two datetimes

Hi, everyone!  I have a flow that triggers once a form submission is complete and emails the submitter all the details of their response.  It works fine but needs to do some calculations to the dates and times that were submitted in the form.  The calculations are done in an excel sheet, but I need to mimic that same calculation in Power Automate.  Here's what I have:

 

 

  • The excel sheet that collects the Form responses stores date and time in separate columns for both take off and landing dates and times (4 columns total)
    • Date is stored as excel 'Date' type, Power Automate returns the value for 1/15/2022 as 44576.
    • Time is stored as a string type, Power Automate returns a string of 0900 for a time that represents 9am
  • Power Automate needs to pull in those two values for each take off date and landing date, store them as a DateTime variable so that PowerAutomate can calculate the number of hours that passed between the two values. After that, I need to return a float for the number of flight hours between the dates and times.  
    • This is all done in my excel sheet using the following calculations to convert hours to decimal flight hours after subtracting take off datetime from landing datetime, =IFERROR(MOD(IF(AD441=1," ",[@Check]*24),24),0)
      • If the total flight hours are say, 15 and a half hours, the total flight hours should report 15.5, not 15:30 
      • AD441 is just a cell to check for errors and return a zero if there is one, formula here: =IFERROR(AC441-AB441,0) 
  • After it performs all these calculations, I believe the PowerAutomate flow will be complete.

Thanks for your help! @PowerAppsWizard @gabibalaban 

 

1 ACCEPTED SOLUTION

Accepted Solutions
v-yujincui-msft
Microsoft
Microsoft

Hi @rhaddad87 ,

 

Do you want to calculate the time difference between two dates?

I have made a test for your reference.

 

1. Here is my Form.

vyujincuimsft_0-1642672463259.png

2. Create a Flow.

substring(outputs('Get_response_details')?['body/TakeOffTime'],0,2)
substring(outputs('Get_response_details')?['body/TakeOffTime'],2,2)
concat(outputs('Get_response_details')?['body/TakeOffDate'],' ',outputs('Compose-starthour'),':',outputs('Compose-startminutes'))


formatDateTime(outputs('Compose-starttime'),'yyyy-MM-ddTHH:mm:ss')
substring(outputs('Get_response_details')?['body/LandTime'],0,2)
substring(outputs('Get_response_details')?['body/LandTime'],2,2)
concat(outputs('Get_response_details')?['body/LandDate'],' ',outputs('Compose-endhour'),':',outputs('Compose-endminutes'))
formatDateTime(outputs('Compose-endtime'),'yyyy-MM-ddTHH:mm:ss')


div(div(mul(sub(ticks(outputs('Compose-endtimeformated')),ticks(outputs('Compose-starttimeformated'))),100),1000000000),60)
div(outputs('Compose-minutesdifference'),60.0)

vyujincuimsft_1-1642673135177.png

vyujincuimsft_2-1642673537012.png

vyujincuimsft_3-1642673643532.png

Result Screenshot:

vyujincuimsft_4-1642673717043.png

 

Best Regards,

Charlie Choi

View solution in original post

2 REPLIES 2
v-yujincui-msft
Microsoft
Microsoft

Hi @rhaddad87 ,

 

Do you want to calculate the time difference between two dates?

I have made a test for your reference.

 

1. Here is my Form.

vyujincuimsft_0-1642672463259.png

2. Create a Flow.

substring(outputs('Get_response_details')?['body/TakeOffTime'],0,2)
substring(outputs('Get_response_details')?['body/TakeOffTime'],2,2)
concat(outputs('Get_response_details')?['body/TakeOffDate'],' ',outputs('Compose-starthour'),':',outputs('Compose-startminutes'))


formatDateTime(outputs('Compose-starttime'),'yyyy-MM-ddTHH:mm:ss')
substring(outputs('Get_response_details')?['body/LandTime'],0,2)
substring(outputs('Get_response_details')?['body/LandTime'],2,2)
concat(outputs('Get_response_details')?['body/LandDate'],' ',outputs('Compose-endhour'),':',outputs('Compose-endminutes'))
formatDateTime(outputs('Compose-endtime'),'yyyy-MM-ddTHH:mm:ss')


div(div(mul(sub(ticks(outputs('Compose-endtimeformated')),ticks(outputs('Compose-starttimeformated'))),100),1000000000),60)
div(outputs('Compose-minutesdifference'),60.0)

vyujincuimsft_1-1642673135177.png

vyujincuimsft_2-1642673537012.png

vyujincuimsft_3-1642673643532.png

Result Screenshot:

vyujincuimsft_4-1642673717043.png

 

Best Regards,

Charlie Choi

WOW!!!! Thank you so much for your time!!!  

 

So, I did figure out how to do this on my own... it does not look as clean as your solution, but it's similar.  I will post it for others to see:

 

rhaddad87_0-1642686799838.png

Basically, I found out that you don't have to split out the hour and the minute in the military time format, you can just divide the military time format by 2400 and append that result to the serial date value.

Helpful resources

Announcements
MPA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

Learn to digitize and optimize business processes and connect all your applications to share data in real time.

Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

May UG Leader Call Carousel 768x460.png

What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.

PA Survey Carousel Image.png

We want to hear from you!

If you are a small business ISV/Reseller, share your thoughts with our research team.

Users online (1,345)