cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
gibs
Level: Power Up

Calculate flight time

I would like to calculate flight time as an example:

 

Departure 2018-01-22 10:05

Arrival 2018-01-22 12:35

Result 2:30

 

Departure 2018-01-22 23:05

Arrival 2018-01-23 12:35

Result 13:30

 

So in my PowerApp i have date and time fields which for departure och arrival and I would like to have a resultsfield wich shows / calculates the flight time.

New to powerapps and would be fantastic if anybody knows. Txs

11 REPLIES 11
Sienna
Level 10

Re: Calculate flight time

Use DateDiff Function

DateDiff(YourDatabase.DepartureColumn,YourDatabase.ArrivalColumn, Hours) - return hours

DateDiff(YourDatabase.DepartureColumn,YourDatabase.ArrivalColumn, Minutes) - return minutes

 

If your columns are not formated corectly then use

DateDiff(DateTimeValue(YourDatabase.DepartureColumn),DateTimeValue(YourDatabase.ArrivalColumn), Hours) 

Sienna
Level 10

Re: Calculate flight time

Sorry I didn't noticed. If you want to round it and fromat it to hh,mm then round it like this

Round(DateDiff(DateTimeValue(YourDatabase.DepartureColumn),DateTimeValue(YourDatabase.ArrivalColumn),Minutes)/60,2)

or

Use text function

Text(DateDiff(DateTimeValue(YourDatabase.DepartureColumn),DateTimeValue(YourDatabase.ArrivalColumn),Minutes)/60,"#.00")

 

Super User
Super User

Re: Calculate flight time

Hi @Sienna

 

I see what you're trying to do there, but I don't think it's quite right to divide the difference in minutes by 60.

If we apply this technique with the first set of times, we get the following result: 2.5.
This is not quite the same as the desired result: 2:30.

One way to format time differences in hh:mm is to find the difference in hours, and to combine that with the ":" symbol, followed by the difference in minutes, minus the difference in hours * 60.

This is how the formula would look:

 

Concatenate(
   Text(
     DateDiff(DateTimeValue("2018-01-22 23:05"),
              DateTimeValue("2018-01-23 12:35"),
              Hours)
    ), 
   ":", 
   Text(
     DateDiff(DateTimeValue("2018-01-22 23:05"),
              DateTimeValue("2018-01-23 12:35"),
              Minutes)
     - (DateDiff(DateTimeValue("2018-01-22 23:05"),
                 DateTimeValue("2018-01-23 12:35"),
                 Hours) * 60
        )
   )
)


Like you say, you would replace the hardcoded values with YourDatabase.ArrivalColumn and YourDatabase.DepartureColumn.

 

If the time difference can be greater than 24hrs, we could apply the same methodology to display the output in the format 'days hh:mm'.

 

Perhaps someone reading this can provide an easier way to do this?

Sienna
Level 10

Re: Calculate flight time

Hi @timl

You are absolutly right. I didn't think that through. Lets try this instead

Time(DateDiff(DateTimeValue("2018-01-22 23:05"),DateTimeValue("2018-01-23 12:35"),Hours),
DateDiff(DateTimeValue("2018-01-22 23:05"),DateTimeValue("2018-01-23 12:35"),Minutes),
00)

This should work better

Sienna
Level 10

Re: Calculate flight time

Which should work for this scenario. Lets hope no flight will take longer then 24 hour Smiley Very Happy

Otherwise we should add also day function to find out day difference

Sienna
Level 10

Re: Calculate flight time

Something like this

 

Text(DateDiff(DateTimeValue("2018-01-24 12:35"),DateTimeValue("2018-02-22 22:01"),Days)) 
&" Days " & 
Text(Time(DateDiff(DateTimeValue("2018-02-22 22:01"),DateTimeValue("2018-01-24 12:35"),Hours),DateDiff(DateTimeValue("2018-02-22 22:01"),DateTimeValue("2018-01-24 12:35"),Minutes),00),"[$-en-GB]hh:mm")

Adding a months is I think too much I guess 

Highlighted
Sienna
Level 10

Re: Calculate flight time

I have just tested it and for whatever reason the Time function calculates the time wrong when date in moved a day plus or minus so I'm posting something what will work finally but it isn't very nice code... I hoped that time function is much more better than that 

 

DateDiff(DateTimeValue("2018-01-22 23:05"),DateTimeValue("2018-01-23 12:35"),Hours) &":"& 
(DateDiff(DateTimeValue("2018-01-22 23:05"),DateTimeValue("2018-01-23 12:35"),Minutes))-
(DateDiff(DateTimeValue("2018-01-22 23:05"),DateTimeValue("2018-01-23 12:35"),Hours)*60)

 

Sienna
Level 10

Re: Calculate flight time

I've been playing with the Time function for a while now and I just can't get this work properly. I think the time function has a bug there or someone can please explain it to me why it just doesn't work when the days are diferent? I've been formating it to text and back to values but it just doesn't work properly. And BTW @timl I just noticed I've posted the same workaround... My appology 😉

Super User
Super User

Re: Calculate flight time

Hi @Sienna

 

I think it's great that you're testing these date functions. Hopefully, it'll help whoever reads this.

 

The Time function just returns a time without a date component. It doesn't return time durations, and perhaps this is where the confusion arises.

 

According to the documentation, here's the signature of the function.

https://docs.microsoft.com/en-us/powerapps/functions/function-date-time

 

Time( Hour, Minute, Second )
Hour - Required. A number that ranges from 0 (12:00 AM) to 23 (11:00 PM).
Minute - Required. A number that ranges from 0 to 59.
Second - Required. A number that ranges from 0 to 59.


 

Let's look at your example where the departure and arrival dates span different days:

 

Time(DateDiff(DateTimeValue("2018-01-22 23:05"),DateTimeValue("2018-01-23 12:35"),Hours),
     DateDiff(DateTimeValue("2018-01-22 23:05"),DateTimeValue("2018-01-23 12:35"),Minutes),
     00)

If we display this formula in a label, we get the result 2:30.

 

Datetime.JPG

 

Why do we get this result?

 

After PowerApps parses the DateDiff functions, the resulting formula is this:

 

Time(13,810,00)

 

This call is invalid because the minute component (810) exceeds the acceptable range of 0 to 59.

 

At this stage, PowerApps will parse 810 minutues as 13hrs and 30 minutes. The resuling formula will be this:

 

Time((13 + 13), 30, 00)

 

Which is equal to this:

 

Time(26, 30, 00)

 

The hour component 26 exceeds the maximum value of 23, and therefore, the function assumes tomorrows date. This is how the function returns 2:30 (ie 2:30AM for tomorrow).

 

Hopefully, that might add some clarity.

 

 

 

Helpful resources

Announcements
thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

Top Kudoed Authors (Last 30 Days)
Users online (6,416)