Business Hours Number Format =
VAR _WorkStart =
TIME( 6, 00, 0 )
VAR _WorkFinish =
TIME( 18, 00, 0 )
VAR _1DayWorkingTime =
DATEDIFF( _WorkStart, _WorkFinish, HOUR )
VAR _StartDate =
DATEVALUE([Start (Date/Time)] ) // Set the start Date column here
VAR _EndDate =
DATEVALUE([Finish (Date/Time)] ) // Set the End Date column here
VAR _StartDateTime =
TIMEVALUE([Start (Date/Time)] ) // Set the start Time column here
VAR _EndDateTime =
TIMEVALUE([Finish (Date/Time)] ) // Set the End Time column here
VAR _DaysBetweenStartFinish =
ADDCOLUMNS( 'Date Table', "DayN", WEEKDAY( [Date], 2 ) ) // Use the Date table here
VAR _WorkingDaysBetweenStartFinish =
COUNTX(FILTER(_DaysBetweenStartFinish,
[Date] > _StartDate && [Date] < _EndDate && [DayN] < 7 && [Holiday Yes/No]="No"),
[DayN]) // Sunday and Saturday are weekend days in this calculation // Set [Holiday = No] to exclude those days from the calculation
VAR _Day1WorkingHour =
IF(WEEKDAY( _StartDate, 2 ) < 7,
( MAX( _WorkFinish, _StartDateTime ) - MAX( _StartDateTime, _WorkStart ) ) * 24,
0)
VAR _LastDayWorkingHour =
IF(WEEKDAY( _EndDate, 2 ) < 7,
( MIN( _WorkFinish, _EndDateTime ) - MIN( _EndDateTime, _WorkStart ) ) * 24,
0)
VAR _Duration =
IF(_StartDate = _EndDate&&WEEKDAY(_StartDate,2)<7,
MAX(( MIN( _WorkFinish, _EndDateTime ) - MAX( _WorkStart, _StartDateTime ) ) * 24,
0),
_WorkingDaysBetweenStartFinish * _1DayWorkingTime + _Day1WorkingHour + _LastDayWorkingHour
)
RETURN
_Duration
