#1
|
|||
|
|||
Machine time cycle calculation
I hope someone can help me with a difficult task. i need to calculate the hours of operation of a machine that has three different cycles. Operational, Stand By, and Shut down. The date and time that the machine is in each state are manually imputed. However i would like excel to calculate the total amount of hours spent in each cycle per day. The problem is that the cycles are completely random, and there may or may not be blank cells in between the data. |
#2
|
||||
|
||||
A pivot table might be the most straightforward answer to this, but your table has merged cells which make it unsuitable for creating such a pivot.
I copied your sheet and unmerged the cells and removed blank columns to create a pivot. Is the attached at cell E49 more or less what you're looking for? Last edited by p45cal; 11-13-2019 at 02:05 AM. |
#3
|
|||
|
|||
Another way.
NB! In case activitie can expand over several days, user has to re-register at every midnight (like I did for activitie from 12.11.2019 13:00 - 13.11.2019 07:00). Otherwise the calculation of time on sheet Statuses will be considerably more complex, or even impossible. |
#4
|
||||
|
||||
Quote:
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. |
#5
|
|||
|
|||
Thank you so much for your help p45cal and ArviLaanemets!
This got me stuck for days! I really appreciate your support. I manage to get it to work myself yesterday, and i thought i post it for anyone else who may have the same problem. Thanks again you guys are great!!!!! |
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Run-time error '462': The remote server machine does not exist or is unavailable | scienceguy | Excel Programming | 3 | 04-15-2019 05:26 PM |
Multiple Timesheets - id'ing two people working on the same machine at the same time. | Will | Excel | 1 | 09-28-2016 09:12 AM |
Macro needed for time calculation and auto insert | ballst | Excel Programming | 8 | 05-21-2015 03:54 PM |
Moving OneNote files from Win 7 machine to a new Win 8.1 Pro machine - how to do it successfully? | WesternGuy | OneNote | 7 | 01-03-2015 02:48 PM |
unable to open .RTF files in server machine from client machine | Naresh_1210 | Word | 0 | 03-12-2010 03:26 AM |