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

[rhaddad87]

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 

[v-yujincui-msft · Accepted solution]

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.

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)

Result Screenshot:

Best Regards,

Charlie Choi

[rhaddad87]

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:

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.