Good afternoon all,
I am trying to find a way to calculate our devices downtime during our Performance Measures hours for each month.
As of now i add each device,from each shift, each day for the entire month. Example:
Performance Measure hours are Monday-Friday 5am to 9pm. We have three shifts and each of these shifts have certain hours that are under Performance Measures (PM):
1st- 6am-2pm - 8 hours under PM
2nd- 2pm-10pm - 7 hours under PM
3rd- 10pm-6am - 1 hour under PM
So, if a devices goes out of service Monday at 3am and comes back into service Wednesday at 4pm the total downtime would be 43 hours, see below:
Monday: Device goes OOS during 3rd shift (10p-6a) at 3am, PM start at 5a so from 5a-6a (1 hour) we are under PM for 3rd shift: so the device is OOS for 1 hour during 3rd shift.
1st shift: the device is OOS for the entire shift (6a-2p 8 hours under PM) so the total is now 9 hours the device has been OOS according to PM
2nd shift: the device is OOS the entire shift (2p-10p) PM's stop at 9pm so from 2p-9p (7 hours) the device is OOS according to the PM's. this now makes the total OOS time 16 hours.
This will repeat for Tuesday bringing the total to 32 hours of downtime during PM's
Wednesday the device comes back into service at 4 pm, so we will have 1 hour for 3rd shift, 8 hours for 1st shift and 2 hours for 2nd shift (2p-4p) bringing the total to 43 hours of downtime.
When doing our shift change log each shift does a "move and copy" to transfer all the formulas and drop boxes, are devices are also listed in a drop box.
Is there a way to have excel keep track of each device for an entire month, or for the length of time the device is OOS? If this isn't possible is there a way to have it calculate for each shift so i can add the shifts together at the end of the month and avoid adding each hour, each device, each shift.
Also i need the total to not be a time and date format, i need ti to read 4 hours and 5 minutes or 2 days 5 hours and 37 minutes.
I am trying to find a way to calculate our devices downtime during our Performance Measures hours for each month.
As of now i add each device,from each shift, each day for the entire month. Example:
Performance Measure hours are Monday-Friday 5am to 9pm. We have three shifts and each of these shifts have certain hours that are under Performance Measures (PM):
1st- 6am-2pm - 8 hours under PM
2nd- 2pm-10pm - 7 hours under PM
3rd- 10pm-6am - 1 hour under PM
So, if a devices goes out of service Monday at 3am and comes back into service Wednesday at 4pm the total downtime would be 43 hours, see below:
Monday: Device goes OOS during 3rd shift (10p-6a) at 3am, PM start at 5a so from 5a-6a (1 hour) we are under PM for 3rd shift: so the device is OOS for 1 hour during 3rd shift.
1st shift: the device is OOS for the entire shift (6a-2p 8 hours under PM) so the total is now 9 hours the device has been OOS according to PM
2nd shift: the device is OOS the entire shift (2p-10p) PM's stop at 9pm so from 2p-9p (7 hours) the device is OOS according to the PM's. this now makes the total OOS time 16 hours.
This will repeat for Tuesday bringing the total to 32 hours of downtime during PM's
Wednesday the device comes back into service at 4 pm, so we will have 1 hour for 3rd shift, 8 hours for 1st shift and 2 hours for 2nd shift (2p-4p) bringing the total to 43 hours of downtime.
When doing our shift change log each shift does a "move and copy" to transfer all the formulas and drop boxes, are devices are also listed in a drop box.
Is there a way to have excel keep track of each device for an entire month, or for the length of time the device is OOS? If this isn't possible is there a way to have it calculate for each shift so i can add the shifts together at the end of the month and avoid adding each hour, each device, each shift.
Also i need the total to not be a time and date format, i need ti to read 4 hours and 5 minutes or 2 days 5 hours and 37 minutes.
