cancel
Showing results for
Did you mean:
Highlighted
New Member

## 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
Highlighted
Memorable Member

## 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)

Highlighted
Memorable Member

## 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")

Highlighted
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?

Highlighted
Memorable Member

## 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

Highlighted
Memorable Member

## Re: Calculate flight time

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

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

Highlighted
Memorable Member

## 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
Memorable Member

## 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)```

Highlighted
Memorable Member

## 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 😉

Highlighted
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.

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.

Announcements

#### Microsoft Ignite

This will be a conference that you do not want to miss!

Reopen responsibly, monitor intelligently, and protect continuously with solutions for a safer work environment.

#### August 2020 CYST Challenge

Check out the winners of the recent 'Can You Solve These?' community challenge!

#### Experience what’s next for Power Apps

Join us for an in-depth look at the new Power Apps features and capabilities at the free Microsoft Business Applications Launch Event.