How to visualize occupancy over time

[lammec01]

I have a basis excel file where per machine I have the start and end time of when it was "in use". I need a timeline (per hour) with the occupied time per machine. For example if a machine was in use from 11u45 till 13u30 ... it was in use 15 min (from 11u), 60 min (from 12u) and 30 min (from 13u). If I can transform my date like that I can visualize this in a bar chart. Any ideas on how to transform my data? 

[lammec01]

Thanks for the response.

I am however missing still some data.

Machine 1 is in use from 10u00 till 11u30 and from 14u00 till 16u15.

I should get result like this

10u --> 60 min occupied

11u --> 30 min occupied

14u --> 60 min occupied

15u --> 60 min occupied

16u --> 15 min occupied

In your solution I am missing 15u timeslot.

[v-stephen-msft]

Hi @lammec01 ,

You can split two column with "u", and then pivot them.

Repeat the operation for Column3.

Duplicate it and append them.

Pivot:

You can download my attachment for more details.

Best Regards,

Stephen Tao

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