Calculate time between On and Off Status rows

[gzzKarlaP]

I have a dataset that logs everytime an equipment gets turn on or off, login its status and the time it was turn on/off. I want to calculate how long its been active by the hour. I want to create 24 columns for each hour and display in there the active time (total minutes that hour). The dataset only logs the current day.  The status represent everytime an equiment has been turn on  (Status 1) or turned off (Status 0). Every row should be a change of status of a given equipment

For example:

• In hour 10:00 it gets turn on at 10:20 and then turn off at 10:45, then it returns 25 minutes.
• If it doesn't get turn off until 11:20 then in the column of hour 10:00 it will give back 40 minutes, and column of hour 11:00 returns 20minutes
• If it doesnt get turn off until 12:20 then
  • Column 10 returns 40
  • Column 11 returns 60
  • Column 12 returns 20
• If there is no log at 9:00 the column return 0

mills_logs.csv

[v-chenwuz-msft]

Hi @gzzKarlaP ,

Among the data you gave, can you explain how the duration of the 7 is calculated.

Best Regards

Community Support Team _ chenwu zhu

[danextian]

Hi @gzzKarlaP ,
Please post a sample data that we can easily manipulate (not an image).