Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 11-12-2019, 12:03 PM
tmoss tmoss is offline Machine time cycle calculation Windows 10 Machine time cycle calculation Office 2019
Novice
Machine time cycle calculation
 
Join Date: Nov 2019
Posts: 2
tmoss is on a distinguished road
Unhappy 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.
Attached Files
File Type: xlsx Machine cycle notes.xlsx (18.9 KB, 7 views)
Reply With Quote
  #2  
Old 11-12-2019, 04:53 PM
p45cal's Avatar
p45cal p45cal is offline Machine time cycle calculation Windows 10 Machine time cycle calculation Office 2019
Expert
 
Join Date: Apr 2014
Posts: 863
p45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant future
Default

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?
Attached Files
File Type: xlsx msofficeforums43814Machine cycle notes.xlsx (29.7 KB, 4 views)

Last edited by p45cal; 11-13-2019 at 02:05 AM.
Reply With Quote
  #3  
Old 11-13-2019, 01:05 AM
ArviLaanemets ArviLaanemets is offline Machine time cycle calculation Windows 8 Machine time cycle calculation Office 2016
Expert
 
Join Date: May 2017
Posts: 869
ArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud of
Default

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.
Attached Files
File Type: xlsx Machine cycle notes.xlsx (27.3 KB, 7 views)
Reply With Quote
  #4  
Old 11-13-2019, 05:53 AM
p45cal's Avatar
p45cal p45cal is offline Machine time cycle calculation Windows 10 Machine time cycle calculation Office 2019
Expert
 
Join Date: Apr 2014
Posts: 863
p45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant future
Default

Quote:
Originally Posted by tmoss View Post
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.
Attached Files
File Type: xlsx msofficeforums43814Machine cycle notes v2.xlsx (25.5 KB, 5 views)
Reply With Quote
  #5  
Old 11-14-2019, 08:01 AM
tmoss tmoss is offline Machine time cycle calculation Windows 10 Machine time cycle calculation Office 2019
Novice
Machine time cycle calculation
 
Join Date: Nov 2019
Posts: 2
tmoss is on a distinguished road
Talking

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!!!!!
Attached Files
File Type: xlsx Machine cycle notes.xlsx (34.5 KB, 9 views)
Reply With Quote
Reply

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

Other Forums: Access Forums

All times are GMT -7. The time now is 01:49 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft