cancel
Showing results for
Did you mean:
Member

## 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!

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team

## Re: Computing Time Metric (Difference of Two Time Values)

Hi @girinpanda,

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]
)
)
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

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 REPLIES 3
Community Support Team

## Re: Computing Time Metric (Difference of Two Time Values)

Hi @girinpanda,

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]
)
)
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

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Member

## Re: Computing Time Metric (Difference of Two Time Values)

Thanks, Dale! I'll try this out.

Community Support Team

## Re: Computing Time Metric (Difference of Two Time Values)

Hi @girinpanda,

Could you please mark the proper answer as solution?

Best Regards!
Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.