Forum Discussion
Computing Time Metric (Difference of Two Time Values)
I'm trying to compute a Time Metric (measured in hours) which is the difference of two datetimes. I can't just use DATEDIFF since I have the following assumptions:
1. Only the hours which falls within 9AM to 5PM will be recorded in Time Metric
2. Only the hours during the WEEKDAYS will be recorded in Time Metric
Here's my computation for the Time Metric:
Time Metric = SWITCH([Shift], 1, [Business Days] * 8 + ([End Time] - [Start Time]) * 24,
2, ([Business Days] * 8) - ([Start Time] - [End Time]) * 24,
3, [Business Days] * 8 + ([5PM] - [Start Time]) * 24,
4, ([Business Days] - 1) * 8 + ([5PM] - [Start Time]) * 24,
5, ([Business Days] - 1) * 8 + ([End Time] - [9AM]) * 24,
6, [Business Days] * 8,
7, ([Business Days] - 1) * 8,
8, ([Business Days] * 8) + ([End Time] - [9AM]) * 24,
9, [Business Days] * 8,
10, ([Business Days] * 8) + 8
)
where [Business Days] is the number of weekdays within two timestamps
& [9AM] and [5PM] are TIMEVALUES for "9:00:00AM" and "5:00:00PM" respectively
and the computation for [SHIFT] is:
Shift = IF(([Start Time] >= [9AM] && [Start Time] <= [5PM]) && ([End Time] >= [9AM]
&& [End Time] <= [5PM]) && ([Start Time] < [End Time]), 1,
IF(([Start Time] >= [9AM] && [Start Time] <= [5PM]) && ([End Time] >= [9AM]
&& [End Time] <= [5PM]) && ([Start Time] > [End Time]), 2,
IF(([Start Time] >= [9AM] && [Start Time] <= [5PM]) && ([End Time] > [5PM]), 3,
IF(([Start Time] >= [9AM] && [Start Time] <= [5PM]) && ([End Time] < [9AM]), 4,
IF(([Start Time] > [5PM]) && ([End Time] >= [9AM] && [End Time] <= [5PM]), 5,
IF([Start Time] > [5PM] && [End Time] > [5PM], 6,
IF([Start Time] > [5PM] && [End Time] < [9AM], 7,
IF([Start Time] < [9AM] && [End Time] >= [9AM] && [End Time] <= [5PM], 8,
IF([Start Time] < [9AM] && [End Time] < [9AM], 9,
IF([Start Time] < [9AM] && [End Time] > [5PM], 10
))))))))))
This formula is already working and returns the correct hours. But is there a way to optimize this formula? Any tips or help would be greatly appreciated!
Thanks! :smileyvery-happy:
Hi Anonymous,
This won't be easy. Please check another solution below. You also can check it step by step in this file.
AllInOne = VAR standardStart = IF ( TIMEVALUE ( [Start Time] ) < TIME ( 9, 0, 0 ), DATEVALUE ( [Start Time] ) + TIME ( 9, 0, 0 ), IF ( TIMEVALUE ( [Start Time] ) > TIME ( 17, 0, 0 ), DATEVALUE ( [Start Time] ) + TIME ( 17, 0, 0 ), [Start Time] ) ) VAR standardEnd = IF ( TIMEVALUE ( [End Time] ) < TIME ( 9, 0, 0 ), DATEVALUE ( [End Time] ) + TIME ( 9, 0, 0 ), IF ( TIMEVALUE ( [End Time] ) > TIME ( 17, 0, 0 ), DATEVALUE ( [End Time] ) + TIME ( 17, 0, 0 ), [End Time] ) ) VAR businessDay = CALCULATE ( SUMX ( DateTable, [IsWorkday] ), FILTER ( 'DateTable', 'DateTable'[Date] > [Start Time].[Date] && 'DateTable'[Date] < [End Time].[Date] ) ) RETURN IF ( [Start Time] > [End Time], 9999, IF ( [Start Time].[Date] = [End Time].[Date], DATEDIFF ( standardstart, standardend, HOUR ), [Business Days] * 8 + DATEDIFF ( TIMEVALUE ( standardstart ), TIME ( 17, 0, 0 ), HOUR ) + DATEDIFF ( TIME ( 9, 0, 0 ), TIMEVALUE ( standardend ), HOUR ) ) )Note: 9999 means Error. A date table is needed.
Best Regards,
Dale
3 Replies
Hi Anonymous,
This won't be easy. Please check another solution below. You also can check it step by step in this file.
AllInOne = VAR standardStart = IF ( TIMEVALUE ( [Start Time] ) < TIME ( 9, 0, 0 ), DATEVALUE ( [Start Time] ) + TIME ( 9, 0, 0 ), IF ( TIMEVALUE ( [Start Time] ) > TIME ( 17, 0, 0 ), DATEVALUE ( [Start Time] ) + TIME ( 17, 0, 0 ), [Start Time] ) ) VAR standardEnd = IF ( TIMEVALUE ( [End Time] ) < TIME ( 9, 0, 0 ), DATEVALUE ( [End Time] ) + TIME ( 9, 0, 0 ), IF ( TIMEVALUE ( [End Time] ) > TIME ( 17, 0, 0 ), DATEVALUE ( [End Time] ) + TIME ( 17, 0, 0 ), [End Time] ) ) VAR businessDay = CALCULATE ( SUMX ( DateTable, [IsWorkday] ), FILTER ( 'DateTable', 'DateTable'[Date] > [Start Time].[Date] && 'DateTable'[Date] < [End Time].[Date] ) ) RETURN IF ( [Start Time] > [End Time], 9999, IF ( [Start Time].[Date] = [End Time].[Date], DATEDIFF ( standardstart, standardend, HOUR ), [Business Days] * 8 + DATEDIFF ( TIMEVALUE ( standardstart ), TIME ( 17, 0, 0 ), HOUR ) + DATEDIFF ( TIME ( 9, 0, 0 ), TIMEVALUE ( standardend ), HOUR ) ) )Note: 9999 means Error. A date table is needed.
Best Regards,
Dale
Hi Anonymous,
Could you please mark the proper answer as solution?
Best Regards!
Dale