Quote:
Originally Posted by tmoss
The date and time that the machine is in each state are manually imputed.
|
When I looked again at your
Total Operational Hours value of 22 in cell K45 I realised that rather than being a random value it was the value that you expected from the data in the table. This led me to realise that your dates and times in columns C and G were not the
duration that the machine was in that state but the date and time of when the machine
started to be in that state, with the duration needing to be calculated using the start of the next state.
I also realised that times up to 7am were being included as part of the previous day (that is, your day starts at 7am). [I hope you always have an entry for 7am each day? If not, a tweak will be required.]
The attached contains some formulae to the right of your table starting at cell BN6 which is the table that is summarised by the pivot table at cell BN47. This table corresponds to your required results.
The dates and times need to be in order on your table.