Forum Discussion

Tauer's avatar
Tauer
New Member
2 years ago
Solved

Calculating the time between two timestamps excluding break times

Hello all,

 

I hope you can help me, since I've searched a lot and couldn't find a solution for my particular problem.

 

My scenario is as follows: I have two timestamps which I need to calculate the difference in minutes BUT if either the creation or the end time is within the break time, I need to subtract the amount of minutes that were within the break time. The break time is from 11:30 to 12:00.

 

Example:

Creation Date TimeConfirmation Date TimeConfirmation DurationDesired Duration
11:02:1712:07:126434
11:14:2012:07:395323
11:21:3612:02:524111
11:21:4212:03:094111
11:16:2411:45:232914
11:44:1212:06:03226

 

I hope there is a solution to my problem.

 

Many thanks in advance.

Need Help
  • bhanu_gautam's avatar
    bhanu_gautam
    2 years ago

    Tauer , First create a caluclated column for Total duration

     

    TotalDuration = DATEDIFF([Creation Date Time], [Confirmation Date Time], MINUTE)

     

    Then create a calculated column for break time adjustment

    BreakStart = TIME(11, 30, 0)
    BreakEnd = TIME(12, 0, 0)

    BreakMinutes =
    VAR CreationTime = TIME(HOUR([Creation Date Time]), MINUTE([Creation Date Time]), SECOND([Creation Date Time]))
    VAR ConfirmationTime = TIME(HOUR([Confirmation Date Time]), MINUTE([Confirmation Date Time]), SECOND([Confirmation Date Time]))
    VAR BreakStartTime = BreakStart
    VAR BreakEndTime = BreakEnd
    VAR OverlapStart = MAX(BreakStartTime, CreationTime)
    VAR OverlapEnd = MIN(BreakEndTime, ConfirmationTime)
    VAR OverlapMinutes = DATEDIFF(OverlapStart, OverlapEnd, MINUTE)
    RETURN
    IF(OverlapStart < OverlapEnd, OverlapMinutes, 0)

     

    In the end create one more column for desired duration

    DAX
    DesiredDuration = [TotalDuration] - [BreakMinutes]

2 Replies

  • bhanu_gautam's avatar
    bhanu_gautam
    Icon for Super User rankSuper User
    2 years ago

    Tauer , First create a caluclated column for Total duration

     

    TotalDuration = DATEDIFF([Creation Date Time], [Confirmation Date Time], MINUTE)

     

    Then create a calculated column for break time adjustment

    BreakStart = TIME(11, 30, 0)
    BreakEnd = TIME(12, 0, 0)

    BreakMinutes =
    VAR CreationTime = TIME(HOUR([Creation Date Time]), MINUTE([Creation Date Time]), SECOND([Creation Date Time]))
    VAR ConfirmationTime = TIME(HOUR([Confirmation Date Time]), MINUTE([Confirmation Date Time]), SECOND([Confirmation Date Time]))
    VAR BreakStartTime = BreakStart
    VAR BreakEndTime = BreakEnd
    VAR OverlapStart = MAX(BreakStartTime, CreationTime)
    VAR OverlapEnd = MIN(BreakEndTime, ConfirmationTime)
    VAR OverlapMinutes = DATEDIFF(OverlapStart, OverlapEnd, MINUTE)
    RETURN
    IF(OverlapStart < OverlapEnd, OverlapMinutes, 0)

     

    In the end create one more column for desired duration

    DAX
    DesiredDuration = [TotalDuration] - [BreakMinutes]