Forum Discussion

quincy_p's avatar
quincy_p
Icon for Advocate I rankAdvocate I
2 years ago

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

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)

 

 

CreatedDateHoursSVMXC__Initial_Response__c
1/3/2024 9:3411/3/2024 10:28
1/3/2024 9:3511/3/2024 10:28
1/3/2024 9:3511/3/2024 10:28
1/3/2024 9:3511/3/2024 10:28
1/3/2024 10:03241/4/2024 10:00
1/3/2024 10:16-21/3/2024 8:30
1/3/2024 10:16-21/3/2024 8:30
1/3/2024 11:02-121/2/2024 23:00

 

Need Help

2 Replies

  • rajendraongole1's avatar
    rajendraongole1
    Icon for Super User rankSuper User
    2 years ago

    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's avatar
      quincy_p
      Icon for Advocate I rankAdvocate I
      2 years ago

      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!