Hello all experts!
Below is a snip of a worksheet from a rather large workbook. What you see in the sheet columns is "Status," "Date & Time," "Calculated Value of change from previous status." "Comments," "Initials"
In general, this is an application (that should have been in a database instead), where a graphic representation of my factory can be clicked on. Basically a click in a cell brings up a form that allows user to change a status from "Running" to "UM-Notif," or any of several status options. When the user submits the form, VB will go to the last line, the next empty line actually, and write the new status selected, and the current date & time (in decimal format). Then calculate for the line above how long the machine was in the previous status. Some of what you see below is just click-through's (Qualify status below was just 18 seconds).
Here's my conundrum... Gosh how Excel stinks dealing with dates and times (that's how I justify it anyhow). I pull status-time each week; literally manually. For a one week report, times several machines, I actually manage to get this done in less than 30 minutes. Can anyone recommend a method for polling this type of data with VB and or a form? The basic hero or heroine (odd word) for me would be that I could enter some limits to the report like start date and time, followed by end date and time. Then the report would pull the data and place the collective "time in each status" as a total for that window. I've attempted this many times in various ways and keep getting all kinds of errors, essentially getting no where. One additional problem was how to handle "partial time." For example if I wanted to pull the data for 2nd shift for the week. This shift is 2pm to 10pm. In the form this time is going to be a machine is "Running" from the previous shift and it gets logged to "UM-Notif" (which stands for Unscheduled Maintenance - Notified: this means machine broke down and they notified maintenance). SO, at 2pm shift change, the machine has been "Running" since 10:50 AM, and get's logged UM-Notif at 6:22 PM (above this is 10/20/22 18:22). The machine was in a running state for 7 hours, 31 minutes, 36 seconds, but only 4:31:36 of that was part of 2nd shift. The same problem occurs at the end of the shift, whether the status is running or UM-something, etc.
Sigh, and my apologies in advance for the heavy and lengthy explanation. I will appreciate any and all inputs.
Shawn
Below is a snip of a worksheet from a rather large workbook. What you see in the sheet columns is "Status," "Date & Time," "Calculated Value of change from previous status." "Comments," "Initials"
In general, this is an application (that should have been in a database instead), where a graphic representation of my factory can be clicked on. Basically a click in a cell brings up a form that allows user to change a status from "Running" to "UM-Notif," or any of several status options. When the user submits the form, VB will go to the last line, the next empty line actually, and write the new status selected, and the current date & time (in decimal format). Then calculate for the line above how long the machine was in the previous status. Some of what you see below is just click-through's (Qualify status below was just 18 seconds).
Qualify | 10/19/22 6:21 | 0:00:18 | ||
UM-Notif | 10/19/22 6:21 | 1:27:49 | rear paddle tense missing | |
UM-Maint | 10/19/22 7:49 | 0:00:38 | ||
UM-Comp | 10/19/22 7:50 | 0:00:05 | changed both paddle cylinders and cleaned 208 fixture | tl |
Qualify | 10/19/22 7:50 | 0:00:05 | ||
Running | 10/19/22 7:50 | 10:11:46 | ||
UM-Notif | 10/19/22 18:02 | 0:04:05 | Wont run. | |
UM-Maint | 10/19/22 18:06 | 0:00:17 | ||
UM-Comp | 10/19/22 18:06 | 1:46:57 | machine had to be put in home position | td |
Qualify | 10/19/22 19:53 | 0:00:04 | ||
Running | 10/19/22 19:53 | 9:50:39 | ||
UM-Notif | 10/20/22 5:44 | 2:49:39 | Door won't open on HMI | |
UM-Maint | 10/20/22 8:33 | 0:00:04 | diagnose no start issue, marker z cylinder on rear was not in basic position, loose sensor | kg |
UM-Comp | 10/20/22 8:33 | 0:04:46 | ||
Qualify | 10/20/22 8:38 | 0:00:05 | ||
Running | 10/20/22 8:38 | 2:02:32 | ||
UM-Notif | 10/20/22 10:41 | 0:09:28 | Cleaning | |
UM-Maint | 10/20/22 10:50 | 0:00:06 | ||
UM-Comp | 10/20/22 10:50 | 0:00:05 | ||
Qualify | 10/20/22 10:50 | 0:00:05 | ||
Running | 10/20/22 10:50 | 7:31:36 | ||
UM-Notif | 10/20/22 18:22 | 0:12:03 | ||
UM-Maint | 10/20/22 18:34 | 0:00:12 | ||
UM-Comp | 10/20/22 18:34 | 0:15:02 | no error found | td |
Qualify | 10/20/22 18:49 | 0:00:14 | ||
Running | 10/20/22 18:50 |
Here's my conundrum... Gosh how Excel stinks dealing with dates and times (that's how I justify it anyhow). I pull status-time each week; literally manually. For a one week report, times several machines, I actually manage to get this done in less than 30 minutes. Can anyone recommend a method for polling this type of data with VB and or a form? The basic hero or heroine (odd word) for me would be that I could enter some limits to the report like start date and time, followed by end date and time. Then the report would pull the data and place the collective "time in each status" as a total for that window. I've attempted this many times in various ways and keep getting all kinds of errors, essentially getting no where. One additional problem was how to handle "partial time." For example if I wanted to pull the data for 2nd shift for the week. This shift is 2pm to 10pm. In the form this time is going to be a machine is "Running" from the previous shift and it gets logged to "UM-Notif" (which stands for Unscheduled Maintenance - Notified: this means machine broke down and they notified maintenance). SO, at 2pm shift change, the machine has been "Running" since 10:50 AM, and get's logged UM-Notif at 6:22 PM (above this is 10/20/22 18:22). The machine was in a running state for 7 hours, 31 minutes, 36 seconds, but only 4:31:36 of that was part of 2nd shift. The same problem occurs at the end of the shift, whether the status is running or UM-something, etc.
Sigh, and my apologies in advance for the heavy and lengthy explanation. I will appreciate any and all inputs.
Shawn
