Forum Discussion
jbrines
Advocate III
7 months agoHelp working out actual time which includes working hours
Hi Guys,
I got help on here to create this code, the issue is that it doesn't take working hours in to consideration, can the code be ammended to take these hours in to consideration.
Working hours
Mon - 8am - 5pm
Tue - 8am - 5pm
Wed 8am - 5pm
Thurs 8am - 5pm
Fri 8am - 3:30pm
Code
Actual Time =
VAR StartDT = SELECTEDVALUE ( 'Results Data'[Created Date] )
VAR EndDT = SELECTEDVALUE ( 'Results Data'[Closed Date] )
RETURN
IF (
ISBLANK ( EndDT ) || EndDT < StartDT,
BLANK(),
VAR StartDate = INT ( StartDT )
VAR EndDate = INT ( EndDT )
VAR SameDay = StartDate = EndDate
-- Check working day
VAR IsStartWorkingDay =
WEEKDAY ( StartDate, 2 ) <= 5 &&
NOT ( StartDate IN UKBankHols )
VAR IsEndWorkingDay =
WEEKDAY ( EndDate, 2 ) <= 5 &&
NOT ( EndDate IN UKBankHols )
-- Full working days between start and end
VAR FullWorkingDays =
MAX (
0,
NETWORKDAYS ( StartDate, EndDate, 1, UKBankHols )
- IF ( IsStartWorkingDay, 1, 0 )
- IF ( IsEndWorkingDay, 1, 0 )
)
-- Partial day contributions (calendar hours)
VAR StartDayFraction =
IF (
IsStartWorkingDay,
1 - ( StartDT - StartDate ),
0
)
VAR EndDayFraction =
IF (
IsEndWorkingDay,
EndDT - EndDate,
0
)
VAR TotalCalendarDays =
IF (
SameDay,
IF ( IsStartWorkingDay, EndDT - StartDT, 0 ),
FullWorkingDays + StartDayFraction + EndDayFraction
)
RETURN
MAX ( 0, TotalCalendarDays )
)Guys,
On reflection the formula is fine, so I don't now need help thanks and thanks to ryan_mayu and FBergamaschi for replying.