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

#### April 4th Copilot Studio Coffee Chat | Recording Now Available

Did you miss the Copilot Studio Coffee Chat on April 4th? This exciting and informative session with Dewain Robinson and Gary Pretty is now available to watch in our Community Galleries!   This AMA discussed how Copilot Studio is using the conversational AI-powered technology to aid and assist in the building of chatbots. Dewain is a Principal Program Manager with Copilot Studio. Gary is a Principal Program Manager with Copilot Studio and Conversational AI. Both of them had great insights to share with the community and answered some very interesting questions!     As part of our ongoing Coffee Chat AMA series, this engaging session gives the Community the unique opportunity to learn more about the latest Power Platform Copilot plans, where we’ll focus, and gain insight into upcoming features. We’re looking forward to hearing from the community at the next AMA, so hang on to your questions!   Watch the recording in the Gallery today: April 4th Copilot Studio Coffee Chat AMA