Generate running time rows/table

[Anonymous]

Hello everyone,

I am trying to get the running time of a machine & I have only the stoppages recorded + few details on the starting/ending time.

Facts that I know is that on a daily basis the machine starts running at 07h00 am & stops at 15h00 pm / weekend off.

Current information available:

The information I need is actual running time as per below:

Any suggestion or idea would be highly appreciated! 😊

[HotChilli · Accepted solution]

The algorithm would be something like:   (Power Query)

Take the original 'Date Fin' Column and add in a record for 0700 for each date . This will end up as the 'Date Debut' column

-> So take the column , separate date and time, remove the time column. Remove duplicates. Add a column for time 0700.  Merge columns to get a table with one column which is 0700 for each date.

Append this table to a table that holds the original 'Date Fin' column. Sort it by datetime.

You'll want to do a similar thing to the original 'Date Debut' column , by adding a 1500 record for each day to make the final 'Date Fin' column.

You then have to find a way of Merging the 2 tables to get the records to line up.  I'm thinking of a new column with just the Date in each table.  Create an index column with in each day so

1.9.20 0700        1.9.20      1

1.9.20 0805        1.9.20      2

.....

2.9.20 0700        2.9.20     1

You can then Merge the 2 tables on Date and Index columns.  After that, you calculate the duration column. 

Take each step carefully.  Good luck.

[Anonymous]

Thank you VERY much for your help!!