Calculate Total Hours (excluding weekends) but include the start and end of the working week.

[quincy_p]

Hi all - having a tough time creating a column that will measure the total hours between 2 date fields that will exclude weekends.

I am using the below formual at the moment which does work to a degree but I realize that this will be taking, for instance, 10-12 PM on a Friday into consideration which I do not want. 

I want to adapt the below code to set the boundaries so that the weekend is considered from 6 PM Friday to 8 AM Monday morning. Then calcualte the rest of the hours as expected. 

I also would like to add another layer that if the value found is Negative, it will convert it to 0 as the negative values can mess the ordering on visuals. 

I have also included some sample data.

Weekday_Hours =
VAR StartDate = 'Work Order ALL'[SVMXC__CreatedDate__c]
VAR EndDate = 'Work Order ALL'[SVMXC__Actual_Initial_Response__c]
VAR TotalHours = DATEDIFF(StartDate, EndDate, HOUR)

VAR StartDateTime = StartDate
VAR EndDateTime = EndDate

VAR TotalDays = DATEDIFF(StartDateTime, EndDateTime, DAY) + 1
VAR WeekendsCount = 
    COUNTROWS(
        FILTER(
            ADDCOLUMNS(
                GENERATESERIES(0, TotalDays - 1, 1),
                "Date", StartDateTime + [Value]
            ),
            WEEKDAY([Date], 2) >= 6
        )
    )

VAR WeekendHours = WeekendsCount * 24

RETURN 
IF(ISBLANK(StartDate) || ISBLANK(EndDate), BLANK(), TotalHours - WeekendHours)

CreatedDate	Hours	SVMXC__Initial_Response__c
1/3/2024 9:34	1	1/3/2024 10:28
1/3/2024 9:35	1	1/3/2024 10:28
1/3/2024 9:35	1	1/3/2024 10:28
1/3/2024 9:35	1	1/3/2024 10:28
1/3/2024 10:03	24	1/4/2024 10:00
1/3/2024 10:16	-2	1/3/2024 8:30
1/3/2024 10:16	-2	1/3/2024 8:30
1/3/2024 11:02	-12	1/2/2024 23:00

[rajendraongole1]

Hi @quincy_p - you can add your DAX with some additional logic to handle these situation. 

Weekday_Hours =
VAR StartDate = 'Work Order ALL'[SVMXC__CreatedDate__c]
VAR EndDate = 'Work Order ALL'[SVMXC__Actual_Initial_Response__c]

-- Ensure dates are not blank
VAR ValidDates = NOT(ISBLANK(StartDate) || ISBLANK(EndDate))

-- Calculate the total hours between the start and end dates
VAR TotalHours = DATEDIFF(StartDate, EndDate, HOUR)

-- Function to calculate work hours within a single day
VAR WorkDayHours =
SWITCH(TRUE(),
WEEKDAY(StartDate, 2) = 6 && HOUR(StartDate) >= 18, 0,
WEEKDAY(StartDate, 2) = 7, 0,
WEEKDAY(EndDate, 2) = 1 && HOUR(EndDate) < 8, 0,
TRUE,
MAX(0, MIN(18, HOUR(EndDate)) - MAX(8, HOUR(StartDate))) +
(MINUTE(EndDate) - MINUTE(StartDate)) / 60
)

-- Calculate the number of full work days excluding weekends
VAR FullWorkDays =
COUNTROWS(
FILTER(
ADDCOLUMNS(
GENERATESERIES(0, DATEDIFF(StartDate, EndDate, DAY) - 1, 1),
"Date", StartDate + [Value]
),
WEEKDAY([Date], 2) < 6
)
)

-- Calculate the total work hours excluding weekends and specific boundaries
VAR TotalWorkHours = FullWorkDays * 8 + WorkDayHours

-- Ensure no negative values
VAR Result = IF(TotalWorkHours < 0, 0, TotalWorkHours)

RETURN
IF(ValidDates, Result, BLANK())

This solution should give you the correct total work hours between two dates, excluding weekends and considering the specific time boundaries.

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

[quincy_p]

Hi @rajendraongole1 

Thanks for your quick response! 

One thing however - this is calculating only working hours it seems? I still want all 24 hours within the week I just want to set the start of that week.

So include all hours between Monday 8 AM and Friday 6 PM.

Below is the result but this should be 19 hrs between not 8.

Does that make sense? 

Thanks!