Need help with diffing times (commented code :) )

[ericonline]

Hello, (didn't want this to get buried because the other thread was marked with a Solution")

I really need help determining the difference between two times. This is quite a challenging undertaking! 

Two Date+Time cards: 

Code (get your wading boots on!): 

//Concatenate the diffed Hours, ":" and diffed Minutes//

Concatenate(  

//Convert the diffed Hours and Minutes to Text//
  
    Text(    

//Perform DateDiff to determine difference between Hours//

        DateDiff(

//Use DateTimeValue to format the text string to Hours//

            DateTimeValue(

//Get start date, hour and minutes into a format that DateTimeValue likes. Note the Char(13)! (clever)//
                    
                    Concatenate(
                        Text(
                            startDate.SelectedDate,DateTimeFormat.ShortDate),
                        Char(13),
                        Text(
                            Concatenate(
                                startHour.Selected.Value,
                                ":",
                                startMinute.Selected.Value),ShortTime)),"en-US"),
            DateTimeValue(

//Get end date, hours and minutes into a format that DateTimeValue likes. 

                    Concatenate(
                        Text(
                            endDate.SelectedDate,DateTimeFormat.ShortDate),
                        Char(13),
                        Text(
                            Concatenate(
                                endHour.Selected.Value,
                                ":",
                                endMinute.Selected.Value),ShortTime)),"en-US"),
        Hours),"[$-en-US]##"),
        
        ":",

//Do the same thing for Minutes//

    Text(
        DateDiff(
            DateTimeValue(
                    Concatenate(
                        Text(
                            startDate.SelectedDate,DateTimeFormat.ShortDate),
                        Char(13),
                        Text(
                            Concatenate(
                                startHour.Selected.Value,
                                ":",
                                startMinute.Selected.Value),ShortTime)),"en-US"),
            DateTimeValue(
                    Concatenate(
                        Text(
                            endDate.SelectedDate,DateTimeFormat.ShortDate),
                        Char(13),
                        Text(
                            Concatenate(
                                endHour.Selected.Value,
                                ":",
                                endMinute.Selected.Value),ShortTime)),"en-US"),
        Minutes),"[$-en-US]##")
)

But this results in: 

How do I get the correct times to display? 

Thanks

[CarlosFigueira]

That's indeed a big expression :). One thing you can use is the Time function - the values passed to it don't necessarily need to be between 0 and 59 for the minutes. If you call Time(1, 75, 0), for example, it will give you a time of 2:15.

You can also make the DateTime values by adding the selected date to the time, instead of converting them to string and then back to date/time (that may not work in all languages, for example). So if you use this expression:

Time(
    0,
    DateDiff(
        startDate.SelectedDate +
            Time(Value(startHour.Selected.Value), Value(startHour.Selected.Value), 0),
        endDate.SelectedDate +
            Time(Value(endHour.Selected.Value), Value(endHour.Selected.Value), 0),
        Minutes),
    0)

You should get the time difference.

[ericonline]

@CarlosFigueira, thank you for taking the time to respond! Its hard to find this info out there. 

I'm not able to get the formula quite right though... Its returning actual time of day, not number of hours. I edited your code a bit as I think the startHour field was duplicated. 

Time(
    0,
    DateDiff(
        startDate.SelectedDate +
            Time(Value(startHour.Selected.Value), Value(startMinute.Selected.Value), 0),
        endDate.SelectedDate +
            Time(Value(endHour.Selected.Value), Value(endMinute.Selected.Value), 0),
        Minutes),
    0)

Please see below for how it reacts: 

Notice how its showing TIME not # of hours. And when I change the endDate to another day, the # of hours doesn't reflect. Any more ideas on what I should check? 

[ericonline]

Hi @CarlosFigueira. I've been deep in this for a day or so. If I could pick your brain again, It'd be much appreciated. 

I almost got it, but the minutes is showing negative numbers when calculating hours that span midnight. 

The label in the top right reflects the code below. Notice the "-30" in the minutes column: 

Here is the code that works UNTIL I span midnight (startDate does not equal endDate): 

Concatenate(
    Text(
        DateDiff(
            startDate.SelectedDate +
                Time(
                    Value(startHour.Selected.Value),
                0, 0),
            endDate.SelectedDate +
                Time(
                    Value(endHour.Selected.Value),
                0, 0),
    Hours),"[$-en-US]##"),

    ":",

    Text(
        If(
            endDate.SelectedDate > startDate.SelectedDate,  //This is for calculating hours that span midnight. Without this, the minutes column is WAY off.//
            DateDiff(
                startDate.SelectedDate +
                Time(0, Value(startMinute.Selected.Value), 0),
                startDate.SelectedDate +
                Time(0, Value(endMinute.Selected.Value), 0),
            Minutes),

            DateDiff(
                startDate.SelectedDate +
                Time(0, Value(startMinute.Selected.Value), 0),
                endDate.SelectedDate +
                Time(0, Value(endMinute.Selected.Value), 0),
            Minutes)
        )
    )
)

[CarlosFigueira]

If you want the data formatted in the hour/minute One way you can implement that is by using the expression below:

Text(
    DateDiff(
        startDate.SelectedDate +
            Time(Value(startHour.Selected.Value); Value(startMinute.Selected.Value); 0);
        endDate.SelectedDate +
            Time(Value(endHour.Selected.Value); Value(endMinute.Selected.Value); 0);
        Hours);
    "00") & ":" &
Text(
    Mod(
        DateDiff(
            startDate.SelectedDate +
                Time(Value(startHour.Selected.Value); Value(startMinute.Selected.Value); 0);
            endDate.SelectedDate +
                Time(Value(endHour.Selected.Value); Value(endMinute.Selected.Value); 0);
            Minutes);
        60);
    "00")

The idea is that you first retrieve the difference in hours (so even if the difference is more than 24 hours, you'll still have the number of hours. Later you can retrieve the difference in minutes, and take the remainder of the division by 60, to get the number of minutes only.

The attached app has some examples of visualizations for the time difference, like in the image below:

[ericonline]

Hi @CarlosFigueira. I really appreciate you time on this issue. Unfortunately this function is not correct when the startTime minutes value > endTime minutes value. 

Example:

• Start a project at 00:30
• Finishe project at 10:20
• Your Hours:Minutes function shows "10:50" but I believe the true answer is 9 hours and 50 minutes. 

Its very apparent in this example: 

• Start project at 00:30
• End at 10:30
• Hours:Minutes looks good

But as soon as startTime minute value is > endTime minute value, the summation is incorrect. 

Here adding 5 minutes ot startTime results in 55 minutes of time being added. 

This was stumping me as well which is why I tried the: 

Text(
        If(
            endDate.SelectedDate > startDate.SelectedDate,  //This is for calculating hours that span midnight. Without this, the minutes column is WAY off.//
            DateDiff(
                startDate.SelectedDate +
                Time(0, Value(startMinute.Selected.Value), 0),
                startDate.SelectedDate +
                Time(0, Value(endMinute.Selected.Value), 0),
            Minutes),

            DateDiff(
                startDate.SelectedDate +
                Time(0, Value(startMinute.Selected.Value), 0),
                endDate.SelectedDate +
                Time(0, Value(endMinute.Selected.Value), 0),
            Minutes)
        )

But this results in weird negative numbers in the minutes column. Man! This is not easy! Do you have any other ideas I can pursue? 

[ericonline]

I think the answer lies somewhere in using your function for the decimal # of hours and the following: 

Text(Time(9, .91666667 * 60,0),"[$-en-US]hh:mm")

Where "9" is the result in hours, ".91666667" is the result in minutes. 

This produces the correct format and such, I just can't seem to figure out how to Split the decimal hours and get them into the above function. 

[CarlosFigueira · Accepted solution]

Good point - I hadn't tested my logic for when the start date is greater than the end date.

One solution is to check whether this is the case, and if so, add a '-' in front of the difference (if you want to indicate that), and make all the calculations with the absolute value (via the Abs function) of the date differences:

"Hours:Minutes: " &
    If(DateDiff(
        startDate.SelectedDate +
            Time(Value(startHour.Selected.Value), Value(startMinute.Selected.Value), 0),
        endDate.SelectedDate +
            Time(Value(endHour.Selected.Value), Value(endMinute.Selected.Value), 0),
        Minutes) < 0,
    "-", "") &

Text(
    Abs(
        DateDiff(
            startDate.SelectedDate +
                Time(Value(startHour.Selected.Value), Value(startMinute.Selected.Value), 0),
            endDate.SelectedDate +
                Time(Value(endHour.Selected.Value), Value(endMinute.Selected.Value), 0),
            Hours)),
    "00") & ":" &
Text(
    Mod(
        Abs(
            DateDiff(
                startDate.SelectedDate +
                    Time(Value(startHour.Selected.Value), Value(startMinute.Selected.Value), 0),
                endDate.SelectedDate +
                    Time(Value(endHour.Selected.Value), Value(endMinute.Selected.Value), 0),
                Minutes)),
        60),
    "00")

The formula should work now (see the attached app ForumThread134813b.msapp), but it's getting way too big - there's a lot of repeated sub-expressions. PowerApps currently doesn't support reusing sub-expressions, so one technique that I've used in the past is to store those expressions in controls (such as labels), and hide them before saving the app. If we have 5 labels with the following expressions:

lblDiffTotalMinutes.Text: DateDiff(
        startDate.SelectedDate +
            Time(Value(startHour.Selected.Value), Value(startMinute.Selected.Value), 0),
        endDate.SelectedDate +
            Time(Value(endHour.Selected.Value), Value(endMinute.Selected.Value), 0),
        Minutes)
lblDiffMinutes.Text: Mod(Abs(Value(lblDiffTotalMinutes.Text)), 60)
lblDiffTotalHours.Text: RoundDown(Abs(Value(lblDiffTotalMinutes.Text)) / 60, 0)
lblDiffHours.Text: Mod(Value(lblDiffTotalHours.Text), 24)
lblDiffDays.Text: RoundDown(Abs(Value(lblDiffTotalHours.Text)) / 24, 0)

Then you can reference their values when displaying the time difference to the user:

If(Value(lblDiffTotalMinutes.Text) < 0, "-", "") &
    Value(lblDiffTotalHours.Text) &
    ":" &
    Text(Value(lblDiffMinutes.Text), "00")

This technique is also shown in the attached app.

Hope this helps!

[ericonline]

Just bumping this up here. @CarlosFigueira , the sample app you provided here is INVALUABLE to anyone needing to diff dateTimes. I refer to it often.

Thanks again!

[testaccPA]

hi, it does not work for me, but strangely it does not throw any mistakes with the reference ... they come aan error by the & (dieser Vefehl erzeugt einen Zirkelverweis - was ist das eigentlich?)
i want to draw in a textx box