Super Challenge: % of time in HH:MM format!

[ericonline]

I went down the rabbit hole with @CarlosFigueira a while back on the process of Diffing two date+times in HH:MM format. (Thank you Carlos!) That has been working fairly well in my application, and I'd recommend people read through and try out the solutions in that post if needing this kind of Time display.

I'm now a bit stuck on the following:

How do I determine the % of one timeframe compared to another in HH:MM format? 

Example: 

• User enters a Planned Start Date, Hour, Minute
• User enters a Planned End Date, Hour, Minute
• User enters an Actual Start Date, Hour, Minute
• User enters an Actual End Date, Hour, Minute
• App calculates the Actual % of Planned timeframe

Code for Planned Duration: 

Concatenate(
    Text(
        DateDiff(
            plannedStart_date.SelectedDate +
                Time(
                    Value(plannedStart_hour.Selected.Value),
                0, 0),
            plannedEnd_date.SelectedDate +
                Time(
                    Value(plannedEnd_hour.Selected.Value),
                0, 0),
    Hours),"[$-en-US]##"),

    ":",

    Text(
        If(
            plannedEnd_date.SelectedDate > plannedStart_date.SelectedDate,  
            DateDiff(
                plannedStart_date.SelectedDate +
                Time(0, Value(plannedStart_minute.Selected.Value), 0),
                plannedStart_date.SelectedDate +
                Time(0, Value(plannedEnd_minute.Selected.Value), 0),
            Minutes) / 60,

            DateDiff(
                plannedStart_date.SelectedDate +
                Time(0, Value(plannedStart_minute.Selected.Value), 0),
                plannedEnd_date.SelectedDate +
                Time(0, Value(plannedEnd_minute.Selected.Value), 0),
            Minutes)
        )
    )
)

Code for Actual Duration: (same as above, just named for Actual Date, Hour, Minute controls)

Concatenate(
    Text(
        DateDiff(
            actualStart_date.SelectedDate +
                Time(
                    Value(actualStart_hour.Selected.Value),
                0, 0),
            actualEnd_date.SelectedDate +
                Time(
                    Value(actualEnd_hour.Selected.Value),
                0, 0),
    Hours),"[$-en-US]##"),

    ":",

    Text(
        If(
            actualEnd_date.SelectedDate > actualStart_date.SelectedDate,  
            DateDiff(
                actualStart_date.SelectedDate +
                Time(0, Value(actualStart_minute.Selected.Value), 0),
                actualStart_date.SelectedDate +
                Time(0, Value(actualEnd_minute.Selected.Value), 0),
            Minutes) / 60,

            DateDiff(
                actualStart_date.SelectedDate +
                Time(0, Value(actualStart_minute.Selected.Value), 0),
                actualEnd_date.SelectedDate +
                Time(0, Value(actualEnd_minute.Selected.Value), 0),
            Minutes)
        )
    )
)

Thank you!

[wyotim]

@ericonlineSo my attempt at a solution is a bit brute-force, but it should work as long as the duration is always in hours and minutes. That is to say, days would become 24-hour additions to the hours, like a day and a half would be 36 hours. If my quick look at your code was correct, that should be the case. Here it is:

/* Convert the Actual Duration into hours and divide it by the conversion of the Planned Duration, also into hours */
Text(
    (
        (
            /* Finds the hour value of the Actual Duration */
            Left(ActualDuration.Text, Find(":", ActualDuration.Text) - 1) 
            +
            /* Finds the minute value of the Actual Duration and converts it into the portion of an hours, adding it to the Acutal Duration hours */
            (
                Right(ActualDuration.Text, Len(ActualDuration.Text) - Find(":", ActualDuration.Text)) / 60
            )
        ) 
        / 
        /* Does the same as above but with the Planned Duration hours and minutes, dividing the Actual Duration result by the Planned Duration result */
        (
            Left(PlannedDuration.Text, Find(":", PlannedDuration.Text) - 1) 
            + 
            (
                Right(PlannedDuration.Text, Len(PlannedDuration.Text) - Find(":", PlannedDuration.Text)) / 60
            )
        )
    /* Converts the result to a percentage */
    ) * 100, "[$-en-US]0.00%"
)

This is a straight-up Excel-style conversion method, so I would be interested to see if anyone has a more elegant response. At any rate, let me know how this attempt works for you! This was a fun issue to think through by the way!

*edit* Corrected a portion of the code that was broken up in a confusing way. If I need to explain the minute conversion code (starting with "Right("), let me know. I didn't really document too well what is happening there.

[CarlosFigueira · Accepted solution]

As expressions start to get more and more complicated, I find it useful to use "helper controls" that will store intermediate calculations, and this way it's easier to catch issues closer to their sources. In this case, if you have a label that stores the difference (in minutes, which is the highest precision that you can find with your date/time pickers), then it's easy to calculate the values based on them.

The attached app shows how this can be done. It shows the helper controls, but in a production app you would set their visibility to false.

Hope this helps!

P.S.: Here are the expressions of that app, based on the variable names that you had in the original post:

hidden_lblPlannedStartDateTime.Text:
    Text(
        plannedStart_date.SelectedDate + Time(Value(plannedStart_hour.Selected.Value), Value(plannedStart_minute.Selected.Value), 0),
        DateTimeFormat.UTC)
hidden_lblPlannedEndDateTime.Text:
    Text(
        plannedEnd_date.SelectedDate + Time(Value(plannedEnd_hour.Selected.Value), Value(plannedEnd_minute.Selected.Value), 0),
        DateTimeFormat.UTC)
hidden_lblPlannedDiffMinutes.Text:
    DateDiff(
        DateTimeValue(hidden_lblPlannedStartDateTime.Text),
        DateTimeValue(hidden_lblPlannedEndDateTime.Text),
        Minutes)

hidden_lblActualStartDateTime.Text:
    Text(
        ActualStart_date.SelectedDate + Time(Value(ActualStart_hour.Selected.Value), Value(ActualStart_minute.Selected.Value), 0),
        DateTimeFormat.UTC)
hidden_lblActualEndDateTime.Text:
    Text(
        ActualEnd_date.SelectedDate + Time(Value(ActualEnd_hour.Selected.Value), Value(ActualEnd_minute.Selected.Value), 0),
        DateTimeFormat.UTC)
hidden_lblActualDiffMinutes.Text:
    DateDiff(
        DateTimeValue(hidden_lblActualStartDateTime.Text),
        DateTimeValue(hidden_lblActualEndDateTime.Text),
        Minutes)

And the expression to calculate the percentage value can be written as:

"% of plan: " &
    RoundDown(
        100 * Value(hidden_lblActualDiffMinutes.Text) / Value(hidden_lblPlannedDiffMinutes.Text),
        2) &
    "%"

[Anonymous]

Great headline and challenge for the community!! 

[ericonline]

@wyotim, thank you for putting some time into looking through the code and devising a solution. I'm very appreciative!

Its challenging sometimes for me to convey the issues I hit while building apps. (I need to get into the habit of peeling the issue out to its own app and posting it along with the question). 

Were you able to open @CarlosFigueira's app and take a peek at what he did. I think the way he changed the Planned and Actual Date/Hour/Minutes to # of minutes the divided accordingly makes for a great solution. He broke it down to "subcontrols" that represent the formula at different stages. Check it out!

Chat soon!

[ericonline]

Thank you @CarlosFigueira for continuing to visit "time functions" with us. You really laid it out in an easy to understand way for me. I'll try to remember to peel my questions out into an app and post them with the question to keep things easier to communicate. 

Great best practice here of using "helper controls" to work through a problem!

Another few examples of the PowerApps community solving real-world problems together. Hope this helps others work with time more efficiently in PowerApps.

Chat soon!

[wyotim]

@ericonlineI think you do a fine job of conveying the issues you are trying to solve; I was a bit light in describing my solution. I haven't looked at @CarlosFigueira's app but I definitely will!

I agree that the usage of subcontrols is a must, especially for the practice of verifying that the proposed solution is working correctly. In looking back at my suggestion, I would probably break up the converted values into their own text labels and have a separate text label for the division step/percentage conversion. This would also allow for handling the output; for instance, if you wanted to only show the percentage if both Planned and Actual had inputs (in the case that maybe there wasn't a planned timeframe or what to show when the Actual hasn't been input yet, etc.). 

Good stuff! Thanks for the exercise and I am glad you found a great solution!