I need to calculate the exact hours between 2 dates which is in the format of mm/dd/yyyy hh:mm:ss. Both the columns having same format. I should exclude the weekends only and no holidays as we are 24/5.

I need to calculate the exact hours between 2 dates which is in the format of mm/dd/yyyy hh:mm:ss. Both the columns having same format. I should exclude the weekends only and no holidays as we are 24/5.

I have added the another calender table and performed the datesbetween. However i got the result in days. I want to more specific on hours.

• powerbi
• dax
• powerbi-desktop

2 Answers 2

You just need to change this line: RETURN DATEDIFF(StartHour, EndHour, HOUR)

to

RETURN DATEDIFF(StartHour, EndHour, MINUTE)

If you wanted to a fractional hour, you'd divide by 60 RETURN DIVIDE(DATEDIFF(StartHour, EndHour, MINUTE),60)

Working24x5Hours =
 VAR StartDateTime = 'Table'[Opened Date]
 VAR EndDateTime = 'Table'[Closed Date]

 -- Convert Start and End Date to only dates for filtering workdays
VAR StartDate = DATEVALUE(StartDateTime)
VAR EndDate = DATEVALUE(EndDateTime)

-- Table of workdays (Monday-Friday) between start and end dates
 VAR WorkDays =
  FILTER(
    ADDCOLUMNS(
        CALENDAR(StartDate, EndDate),
        "Weekday", WEEKDAY([Date], 2),  -- Monday = 1, Sunday = 7
        "IsFirstDay", [Date] = StartDate,
        "IsLastDay", [Date] = EndDate
    ),
    [Weekday] <= 5  -- Exclude Saturday (6) & Sunday (7)
)

 -- Calculate exact working hours per day
 VAR TotalHours =
  SUMX(
    WorkDays,
    VAR CurrentDate = [Date]
    VAR IsFirst = [IsFirstDay]
    VAR IsLast = [IsLastDay]
    
    VAR StartHour = IF(IsFirst, StartDateTime, CurrentDate & " 00:00:00")
    VAR EndHour = IF(IsLast, EndDateTime, CurrentDate & " 23:59:59")
    
    RETURN DATEDIFF(StartHour, EndHour, HOUR)
)

    RETURN TotalHours

• Identifies only weekdays
• Handles cases where the start and end date fall on the same day
• Correctly calculates for multi-day durations

Here is how it works For 1/4/2025 10:30:55 to 1/7/2025 12:30:55

• 1/4 (Saturday) & 1/5 (Sunday) ignored
• 1/6 (Monday): 24 hours
• 1/7 (Tuesday): 12.5 hours

Total: 50 hours