Calculate working hours between two dates (exclude weekends)

[JoseLapeluz]

I've been looking for this kind of help in various forums and I struggled finding something that could help me.

I was working in an app that calculates the SLA hours between two dates, but you can implement my solution into a simple case that you need the working hours between two dates. 

This formula excludes the weekends and only calculates the working hours between 8:30 am to 5:30 pm.

Let me show the app for better understanding.

The two dates are the "Request date" and "Confirmation date" fields. The result is the column "SLA (hours)"

My solution is the next one:

Round(
DateDiff
(
    If(Weekday(var_StartDate) = 1,
    DateTimeValue(DateValue(DateAdd(var_StartDate,1,TimeUnit.Days))+TimeValue("8:30:00 AM")),

    Weekday(var_StartDate) = 7,
    DateTimeValue(DateValue(DateAdd(var_StartDate,2,TimeUnit.Days))+TimeValue("8:30:00 AM")),

    TimeValue(var_StartDate) > TimeValue("5:30:00 PM") And Weekday(var_StartDate) <> 6, 
    DateTimeValue(DateValue(DateAdd(var_StartDate,1,TimeUnit.Days))+TimeValue("8:30:00 AM")),

    TimeValue(var_StartDate) > TimeValue("5:30:00 PM") And Weekday(var_StartDate) = 6,
    DateTimeValue(DateValue(DateAdd(var_StartDate,3,TimeUnit.Days))+TimeValue("8:30:00 AM")),

    TimeValue(var_StartDate) < TimeValue("8:30:00 AM") And Weekday(var_StartDate) <> 1 And Weekday(var_StartDate) <> 7,
    DateTimeValue(DateValue(var_StartDate)+TimeValue("8:30:00 AM")),

    var_StartDate
    ),
    var_EndDate,
    TimeUnit.Days
)*9 +
    Hour(var_EndDate)-
    Hour(
     If(Weekday(var_StartDate) = 1,
    DateTimeValue(DateValue(DateAdd(var_StartDate,1,TimeUnit.Days))+TimeValue("8:30:00 AM")),

    Weekday(var_StartDate) = 7,
    DateTimeValue(DateValue(DateAdd(var_StartDate,2,TimeUnit.Days))+TimeValue("8:30:00 AM")),

    TimeValue(var_StartDate) > TimeValue("5:30:00 PM") And Weekday(var_StartDate) <> 6, 
    DateTimeValue(DateValue(DateAdd(var_StartDate,1,TimeUnit.Days))+TimeValue("8:30:00 AM")),

    TimeValue(var_StartDate) > TimeValue("5:30:00 PM") And Weekday(var_StartDate) = 6,
    DateTimeValue(DateValue(DateAdd(var_StartDate,3,TimeUnit.Days))+TimeValue("8:30:00 AM")),

    TimeValue(var_StartDate) < TimeValue("8:30:00 AM") And Weekday(var_StartDate) <> 1 And Weekday(var_StartDate) <> 7,
    DateTimeValue(DateValue(var_StartDate)+TimeValue("8:30:00 AM")),

    var_StartDate
    )  
    )
    +

        (Minute(var_EndDate)-
    Minute(
     If(Weekday(var_StartDate) = 1,
    DateTimeValue(DateValue(DateAdd(var_StartDate,1,TimeUnit.Days))+TimeValue("8:30:00 AM")),

    Weekday(var_StartDate) = 7,
    DateTimeValue(DateValue(DateAdd(var_StartDate,2,TimeUnit.Days))+TimeValue("8:30:00 AM")),

    TimeValue(var_StartDate) > TimeValue("5:30:00 PM") And Weekday(var_StartDate) <> 6, 
    DateTimeValue(DateValue(DateAdd(var_StartDate,1,TimeUnit.Days))+TimeValue("8:30:00 AM")),

    TimeValue(var_StartDate) > TimeValue("5:30:00 PM") And Weekday(var_StartDate) = 6,
    DateTimeValue(DateValue(DateAdd(var_StartDate,3,TimeUnit.Days))+TimeValue("8:30:00 AM")),

    TimeValue(var_StartDate) < TimeValue("8:30:00 AM") And Weekday(var_StartDate) <> 1 And Weekday(var_StartDate) <> 7,
    DateTimeValue(DateValue(var_StartDate)+TimeValue("8:30:00 AM")),

    var_StartDate
    )  
    ))/60,1

)

Let me explain each part of the code.

First, this part that repeats three times in the code:

If(
    Weekday(var_StartDate) = 1,
    DateTimeValue(DateValue(DateAdd(var_StartDate,1,TimeUnit.Days))+TimeValue("8:30:00 AM")),

    Weekday(var_StartDate) = 7,
    DateTimeValue(DateValue(DateAdd(var_StartDate,2,TimeUnit.Days))+TimeValue("8:30:00 AM")),

    TimeValue(var_StartDate) > TimeValue("5:30:00 PM") And Weekday(var_StartDate) <> 6, 
    DateTimeValue(DateValue(DateAdd(var_StartDate,1,TimeUnit.Days))+TimeValue("8:30:00 AM")),

    TimeValue(var_StartDate) > TimeValue("5:30:00 PM") And Weekday(var_StartDate) = 6,
    DateTimeValue(DateValue(DateAdd(var_StartDate,3,TimeUnit.Days))+TimeValue("8:30:00 AM")),

    TimeValue(var_StartDate) < TimeValue("8:30:00 AM") And Weekday(var_StartDate) <> 1 And Weekday(var_StartDate) <> 7,
    DateTimeValue(DateValue(var_StartDate)+TimeValue("8:30:00 AM")),

    var_StartDate
)

This section give us as a result the correct Start date depending in the date or time or the original Request Date.

It has 5 conditions when the Start date should be different from the original.

• If the Request date is on Sundays, the start date should be the next monday at 8:30am (the formula adds one day and defines 8:30 am the time value).
• If the Request date is on Saturdays, the start date should be the next monday at 8:30am (the formula adds two days and defines 8:30 am the time value).
• If the Request date is not on Fridays but the time value is past 5:30 pm, the start date should be the next day at 8:30am (the formula adds one day and defines 8:30 am the time value).
• If the Request date is on Fridays but the time value is past 5:30 pm, the start date should be the next monday at 8:30am (the formula adds three days and defines 8:30 am the time value).
• If the Request date is any weekday (mon, tue, wed, thurs, fri) but the time value is before 8:30 am, the start date should be the same day but at 8:30am (the formula define the same day and defines 8:30 am the time value).

And if there's not any of these conditions, the start date is the same than the original request date.

I know, we could define a previous variable with this part. So lets made it like that.

Set
(
    var_ActualDate,
    If(Weekday(var_StartDate) = 1,
    DateTimeValue(DateValue(DateAdd(var_StartDate,1,TimeUnit.Days))+TimeValue("8:30:00 AM")),

    Weekday(var_StartDate) = 7,
    DateTimeValue(DateValue(DateAdd(var_StartDate,2,TimeUnit.Days))+TimeValue("8:30:00 AM")),

    TimeValue(var_StartDate) > TimeValue("5:30:00 PM") And Weekday(var_StartDate) <> 6, 
    DateTimeValue(DateValue(DateAdd(var_StartDate,1,TimeUnit.Days))+TimeValue("8:30:00 AM")),

    TimeValue(var_StartDate) > TimeValue("5:30:00 PM") And Weekday(var_StartDate) = 6,
    DateTimeValue(DateValue(DateAdd(var_StartDate,3,TimeUnit.Days))+TimeValue("8:30:00 AM")),

    TimeValue(var_StartDate) < TimeValue("8:30:00 AM") And Weekday(var_StartDate) <> 1 And Weekday(var_StartDate) <> 7,
    DateTimeValue(DateValue(var_StartDate)+TimeValue("8:30:00 AM")),

    var_StartDate
    )
);

Round
(
    DateDiff
    (
        var_ActualDate,var_EndDate,TimeUnit.Days
    )*9 
    
    + Hour(var_EndDate) - Hour(var_ActualDate)
    + (Minute(var_EndDate) - Minute(var_ActualDate))/60,
    1

)

I think it is clearer for understanding in this way. At the end, there is a simple DateDiff between the result of the first code and the original EndDate. The units of the DateDiff are in Days. If we multiply the days by 9 (total of working hours per day), we get the full working hours between the two dates (excluding the time value of the dates).

To get the working hours including the actual time value of each date, we made a subtraction of the hours of both dates and the subtraction of the minutes of both dates divided by 60.

So that's it. I hope my post help you. This is my first time posting something of PowerApps in a forum, but I really think that my work could help someone else.

[BCLS776]

Thanks for sharing. I'll mark this "solved" so that others can benefit from your idea.

Bryan

[BCLS776 · Accepted solution]

Solution provided by @JoseLapeluz :

Round(
DateDiff
(
    If(Weekday(var_StartDate) = 1,
    DateTimeValue(DateValue(DateAdd(var_StartDate,1,TimeUnit.Days))+TimeValue("8:30:00 AM")),

    Weekday(var_StartDate) = 7,
    DateTimeValue(DateValue(DateAdd(var_StartDate,2,TimeUnit.Days))+TimeValue("8:30:00 AM")),

    TimeValue(var_StartDate) > TimeValue("5:30:00 PM") And Weekday(var_StartDate) <> 6, 
    DateTimeValue(DateValue(DateAdd(var_StartDate,1,TimeUnit.Days))+TimeValue("8:30:00 AM")),

    TimeValue(var_StartDate) > TimeValue("5:30:00 PM") And Weekday(var_StartDate) = 6,
    DateTimeValue(DateValue(DateAdd(var_StartDate,3,TimeUnit.Days))+TimeValue("8:30:00 AM")),

    TimeValue(var_StartDate) < TimeValue("8:30:00 AM") And Weekday(var_StartDate) <> 1 And Weekday(var_StartDate) <> 7,
    DateTimeValue(DateValue(var_StartDate)+TimeValue("8:30:00 AM")),

    var_StartDate
    ),
    var_EndDate,
    TimeUnit.Days
)*9 +
    Hour(var_EndDate)-
    Hour(
     If(Weekday(var_StartDate) = 1,
    DateTimeValue(DateValue(DateAdd(var_StartDate,1,TimeUnit.Days))+TimeValue("8:30:00 AM")),

    Weekday(var_StartDate) = 7,
    DateTimeValue(DateValue(DateAdd(var_StartDate,2,TimeUnit.Days))+TimeValue("8:30:00 AM")),

    TimeValue(var_StartDate) > TimeValue("5:30:00 PM") And Weekday(var_StartDate) <> 6, 
    DateTimeValue(DateValue(DateAdd(var_StartDate,1,TimeUnit.Days))+TimeValue("8:30:00 AM")),

    TimeValue(var_StartDate) > TimeValue("5:30:00 PM") And Weekday(var_StartDate) = 6,
    DateTimeValue(DateValue(DateAdd(var_StartDate,3,TimeUnit.Days))+TimeValue("8:30:00 AM")),

    TimeValue(var_StartDate) < TimeValue("8:30:00 AM") And Weekday(var_StartDate) <> 1 And Weekday(var_StartDate) <> 7,
    DateTimeValue(DateValue(var_StartDate)+TimeValue("8:30:00 AM")),

    var_StartDate
    )  
    )
    +

        (Minute(var_EndDate)-
    Minute(
     If(Weekday(var_StartDate) = 1,
    DateTimeValue(DateValue(DateAdd(var_StartDate,1,TimeUnit.Days))+TimeValue("8:30:00 AM")),

    Weekday(var_StartDate) = 7,
    DateTimeValue(DateValue(DateAdd(var_StartDate,2,TimeUnit.Days))+TimeValue("8:30:00 AM")),

    TimeValue(var_StartDate) > TimeValue("5:30:00 PM") And Weekday(var_StartDate) <> 6, 
    DateTimeValue(DateValue(DateAdd(var_StartDate,1,TimeUnit.Days))+TimeValue("8:30:00 AM")),

    TimeValue(var_StartDate) > TimeValue("5:30:00 PM") And Weekday(var_StartDate) = 6,
    DateTimeValue(DateValue(DateAdd(var_StartDate,3,TimeUnit.Days))+TimeValue("8:30:00 AM")),

    TimeValue(var_StartDate) < TimeValue("8:30:00 AM") And Weekday(var_StartDate) <> 1 And Weekday(var_StartDate) <> 7,
    DateTimeValue(DateValue(var_StartDate)+TimeValue("8:30:00 AM")),

    var_StartDate
    )  
    ))/60,1

)

[shine_d]

Hi, I have the same case but how about the start date is like 7pm and end date is 3:30pm until Saturday? Thanks