Get time difference between two dates and times

[iwonder]

Thank you for taking the time to read my question.

On my canvas app I have a start and stop date picker, a start and stop hour drop down (0-23) and a start and stop minute drop down (00-59).

I need to find the time difference in Hours:Minutes between the selected values.

I found this post however if I set the values as seen below I get 2:30. I am expecting 1:30 as there is one hour and 30 minutes difference between the Start Time and Stop Time.

What am I doing wrong?

Thanks

[iwonder · Accepted solution]

Hi,

I edited my formula to the following. It seems to work. Is there a better way?

RoundDown(DateDiff(
    Time(
        Value(StartTimeHour_2.Selected.Value),
        Value(StartTimeMinute_2.Selected.Value),
        0
    ),
    Time(
        Value(StopTimeHour_2.Selected.Value),
        Value(StopTimeMinute_2.Selected.Value),
        0
    ),
    Minutes
)/60,0) & ":" & Mod(DateDiff(
    Time(
        Value(StartTimeHour_2.Selected.Value),
        Value(StartTimeMinute_2.Selected.Value),
        0
    ),
    Time(
        Value(StopTimeHour_2.Selected.Value),
        Value(StopTimeMinute_2.Selected.Value),
        0
    ),
    Minutes),60)

[RandyHayes]

@iwonder 

When dealing with multiple controls like that, I like to shortcut a little bit.

What I will do is put a label on the screen, in your case, let's say 2 labels called lblStartTime and lblStopTime

Set the text property of the lblStartTime to:

Text(yourDatePicker.SelectedDate, ShortDate) & " " & 
StartTimeHour_2.Selected.Value & ":" &
StartTimeMinute_2.Selected.Value & ":00"

And lblStopTime text property to:

Text(yourDatePicker.SelectedDate, ShortDate) & " " & 
StopTimeHour_2.Selected.Value & ":" &
StopTimeMinute_2.Selected.Value & ":00"​

You can then make the visible property of both of these false so users don't see them.

Now...for your original run time formula:

With({mins:DateDiff(DateTimeValue(lblStartTime.Text), DateTimeValue(lblStopTime.Text), Minutes)},
    Text(RoundDown(mins/60, 0), "00") & ":" & Text(Mod(mins, 60), "00")
)

I hope this is helpful for you.

[iwonder]

Thanks Randy.

Just so I follow your answer, you're splitting the formula into a few parts instead of having one big formula but in the end it's the same?

Thanks again for your reply.

[RandyHayes]

@iwonder 

Yes!   I like to think of it as something I often do in Excel (and PowerApps was designed after many concepts of Excel) where you have a relatively complex formula to solve.  I break it down into increments - in one cell I will put one formula to do some initial calculation, then in another cell I will reference that previous cell to use the result of that calculation and so on.  Kind of the same concept here.

However in this case, I often find that I will want to reference the dates/times of the pickers and dropdowns often in the app.  If so, then having this "helper" label with the parts put together already saves me a lot of formula typing.

[iwonder]

Thank you Randy, I appreciate your replies.

Have a great day