cancel
Showing results for
Did you mean:
Regular Visitor

Calculate working hours between two dates (exclude weekends)

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,

Weekday(var_StartDate) = 7,

TimeValue(var_StartDate) > TimeValue("5:30:00 PM") And Weekday(var_StartDate) <> 6,

TimeValue(var_StartDate) > TimeValue("5:30:00 PM") And Weekday(var_StartDate) = 6,

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,

Weekday(var_StartDate) = 7,

TimeValue(var_StartDate) > TimeValue("5:30:00 PM") And Weekday(var_StartDate) <> 6,

TimeValue(var_StartDate) > TimeValue("5:30:00 PM") And Weekday(var_StartDate) = 6,

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,

Weekday(var_StartDate) = 7,

TimeValue(var_StartDate) > TimeValue("5:30:00 PM") And Weekday(var_StartDate) <> 6,

TimeValue(var_StartDate) > TimeValue("5:30:00 PM") And Weekday(var_StartDate) = 6,

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,

Weekday(var_StartDate) = 7,

TimeValue(var_StartDate) > TimeValue("5:30:00 PM") And Weekday(var_StartDate) <> 6,

TimeValue(var_StartDate) > TimeValue("5:30:00 PM") And Weekday(var_StartDate) = 6,

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,

Weekday(var_StartDate) = 7,

TimeValue(var_StartDate) > TimeValue("5:30:00 PM") And Weekday(var_StartDate) <> 6,

TimeValue(var_StartDate) > TimeValue("5:30:00 PM") And Weekday(var_StartDate) = 6,

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Super User

Solution provided by @JoseLapeluz :

``````Round(
DateDiff
(
If(Weekday(var_StartDate) = 1,

Weekday(var_StartDate) = 7,

TimeValue(var_StartDate) > TimeValue("5:30:00 PM") And Weekday(var_StartDate) <> 6,

TimeValue(var_StartDate) > TimeValue("5:30:00 PM") And Weekday(var_StartDate) = 6,

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,

Weekday(var_StartDate) = 7,

TimeValue(var_StartDate) > TimeValue("5:30:00 PM") And Weekday(var_StartDate) <> 6,

TimeValue(var_StartDate) > TimeValue("5:30:00 PM") And Weekday(var_StartDate) = 6,

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,

Weekday(var_StartDate) = 7,

TimeValue(var_StartDate) > TimeValue("5:30:00 PM") And Weekday(var_StartDate) <> 6,

TimeValue(var_StartDate) > TimeValue("5:30:00 PM") And Weekday(var_StartDate) = 6,

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

)``````
_________________________________________________________________________________________
Help the community help more users by choosing to "Accept as Solution" if this post met your needs. If you liked the post and want to show some appreciation, please give it a Thumbs Up.
3 REPLIES 3
Super User

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

Bryan

_________________________________________________________________________________________
Help the community help more users by choosing to "Accept as Solution" if this post met your needs. If you liked the post and want to show some appreciation, please give it a Thumbs Up.
Super User

Solution provided by @JoseLapeluz :

``````Round(
DateDiff
(
If(Weekday(var_StartDate) = 1,

Weekday(var_StartDate) = 7,

TimeValue(var_StartDate) > TimeValue("5:30:00 PM") And Weekday(var_StartDate) <> 6,

TimeValue(var_StartDate) > TimeValue("5:30:00 PM") And Weekday(var_StartDate) = 6,

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,

Weekday(var_StartDate) = 7,

TimeValue(var_StartDate) > TimeValue("5:30:00 PM") And Weekday(var_StartDate) <> 6,

TimeValue(var_StartDate) > TimeValue("5:30:00 PM") And Weekday(var_StartDate) = 6,

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,

Weekday(var_StartDate) = 7,

TimeValue(var_StartDate) > TimeValue("5:30:00 PM") And Weekday(var_StartDate) <> 6,

TimeValue(var_StartDate) > TimeValue("5:30:00 PM") And Weekday(var_StartDate) = 6,

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

)``````
_________________________________________________________________________________________
Help the community help more users by choosing to "Accept as Solution" if this post met your needs. If you liked the post and want to show some appreciation, please give it a Thumbs Up.
Helper III

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

Announcements

Announcing Power Apps Copilot Cookbook Gallery

We are excited to share that the all-new Copilot Cookbook Gallery for Power Apps is now available in the Power Apps Community, full of tips and tricks on how to best use Microsoft Copilot as you develop and create in Power Apps. The new Copilot Cookbook is your go-to resource when you need inspiration--or when you're stuck--and aren't sure how to best partner with Copilot while creating apps.   Whether you're looking for the best prompts or just want to know about responsible AI use, visit Copilot Cookbook for regular updates you can rely on--while also serving up some of your greatest tips and tricks for the Community. Check Out the new Copilot Cookbook for Power Apps today: Copilot Cookbook - Power Platform Community.  We can't wait to see what you "cook" up!