Calculate flight time

[gibs]

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

[Sienna]

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]

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

[timl]

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]

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]

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

[Sienna]

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 

[Sienna]

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]

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 😉

[timl]

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.