cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Super User
Super User

Need help with diffing times (commented code :) )

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: 

dateDiffWithHoursAndMinutes.png

 

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: 

dateDiffWithHoursAndMinutes1.png

 

How do I get the correct times to display? 


Thanks

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
PowerApps Staff CarlosFigueira
PowerApps Staff

Re: Need help with diffing times (commented code :) )

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!

8 REPLIES 8
PowerApps Staff CarlosFigueira
PowerApps Staff

Re: Need help with diffing times (commented code :) )

That's indeed a big expression Smiley Happy. 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.

Super User
Super User

Re: Need help with diffing times (commented code :) )

@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: 

timeDiff.gif

 

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? 

Super User
Super User

Re: Need help with diffing times (commented code :) )

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. 

 

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: 

diffTimes.png

 

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)
        )
    )
)
PowerApps Staff CarlosFigueira
PowerApps Staff

Re: Need help with diffing times (commented code :) )

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:

ForumPost001.PNG

Super User
Super User

Re: Need help with diffing times (commented code :) )

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. 

timeIncorrect.png

Its very apparent in this example: 

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

timeIssue1.png

 

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. 

timeIssue2.png

 

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? 

Super User
Super User

Re: Need help with diffing times (commented code :) )

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. 

Highlighted
PowerApps Staff CarlosFigueira
PowerApps Staff

Re: Need help with diffing times (commented code :) )

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!

Super User
Super User

Re: Need help with diffing times (commented code :) )

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!

Helpful resources

Announcements
firstImage

PowerApps Monthly Community Call!

Join us next Wednesday for our Demo Extravaganza, October 16, 2019 8am PDT.

firstImage

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Top Community Contributors for September  2019

Top Community Contributors for September 2019 🎉🎉

Let's thank our top community contributors

firstImage

Watch Sessions On Demand!

Continue your learning in our online communities.

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

FirstImage

Power Platform World Tour

Coming to a city near you

thirdimage

PowerApps Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

FourthImage

Join PowerApps User Group!!

Connect, share, and learn with your peers year-round

Top Kudoed Authors
Users Online
Currently online: 187 members 5,372 guests
Recent signins:
Please welcome our newest community members: