Calculate hours and minutes spent in one State or another

[TomasPac]

I have this data that is timed by intervals, of which state machinery is in: 

TIME:         STATE:

7:59           Stopped

8:00           Stopped

8:01           Moving

8:02           Moving

8:03           Stopped

I need to calculate the time spent while state = moving, and state = stopped, in hours and minutes.

Something like this: Stopped - 2:21, Moving - 3:45

[sturlaws · Accepted solution]

Hi @TomasPac 

if your data is like your sample date, you can use this measure:

Measure =
INT ( COUNT ( 'Table'[Time] ) / 60 ) & ":"
    & RIGHT (
        0
            & COUNT ( 'Table'[Time] )
                - (
                    INT ( COUNT ( 'Table'[Time] ) / 60 ) * 60
                ),
        2
    )

then add [State] and [Measure] to a table visual to obtain this:

Cheers,
Sturla

If this post helps, then please consider Accepting it as the solution. Kudos are nice too.

[TomasPac]

Something's not right. My time actually is hh:mm:ss AM/PM maybe that's the case, but I see irregularity. I've used the formula you provided and counted one day from 7:52 AM till 16:57, which if calculates is 9 hours 5 minutes apart. But my total result for that day is 10 hours 39 minutes, wheres the problem?? Maybe it's because of the AM/PM difference?

[sturlaws]

It is not caused by AM/PM change. I have created a dataset which has one status pr minute from 7:52 to 16:57, and the measure returns 9:06. Which means that there are some issues in your data set. Either duplicate rows or an interval with two statuses.

[TomasPac]

What about the seconds in my format?? Because the result is completely wrong for me.. In my data State = Moving shows from 2:58:24 PM till 4:57:00 PM, the calculation says that state Moving was for 8:34

[sturlaws]

As the title of your question was ...hours and minutes... and you provided sample data in the form 7:59,8:00,8:01,8:02,.., I assumed your time data where in the format hh:mm, and that the state would be updated every minute. If this is not the case, the solution I suggested will not work.

If your actual data is not like the sample data in your original post, could you please post a sample dataset which has the same properties as your actual data? Or post a excerpt from your data?

[TomasPac]

My time data format is this 8:00:00

[sturlaws]

There are many ways of doing this, I came up with one suggestion, based on the information you gave in your original post, which was hours:minutes, and taking advantage of the fact that your sample data has intervals of 1 minute. If this is not representative for your data, please post a sample dataset which is representative for your data.