Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding times but excluding a time range
Is there a function similar to Networkday for time? That would exclude a
time range from calculations? I need to compute the start and end time of an order. I know how long the job will take and the initial start time but the availability of the press is not 24/7 so I have a couple of problems. Let's say the job goes to press at 6am Monday and is scheduled for 28 hours of production. If the press ran 24/7 then the end time/date would be Tuesday at 10am. Unfortunately the press is down from 4pm to 8pm on Monday and so will not be done until Tuesday at 2pm. I'm generating data for multiple orders over several days and the press availability time may shift from day to day. So the end time of a job cannot land during one of the down periods or take account the downtime periods when figuring the end time of an order. Any help would be appreciated--even if it's just pointing me in the right direction. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding times but excluding a time range
Hi,
The set up on this is important, but try this: RunTime StartDate/Time DownTime FinishDate/Time 1.16666 4/18/05 6:00 AM 1/0/00 4:00 AM 4/19/05 2:00 PM =28/24 =4/24 =+B2+A2+C2 Format is date and Time Format is date and Time ColA ColB ColC ColD 28 hrs/24 4hrs/24 Thanks, "Paulymon" wrote: Is there a function similar to Networkday for time? That would exclude a time range from calculations? I need to compute the start and end time of an order. I know how long the job will take and the initial start time but the availability of the press is not 24/7 so I have a couple of problems. Let's say the job goes to press at 6am Monday and is scheduled for 28 hours of production. If the press ran 24/7 then the end time/date would be Tuesday at 10am. Unfortunately the press is down from 4pm to 8pm on Monday and so will not be done until Tuesday at 2pm. I'm generating data for multiple orders over several days and the press availability time may shift from day to day. So the end time of a job cannot land during one of the down periods or take account the downtime periods when figuring the end time of an order. Any help would be appreciated--even if it's just pointing me in the right direction. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding times but excluding a time range
Thanks for your response.
What I'm having a problem with is what happens when the start time + the order run time lands in the middle of the down time. It would have to be adjusted for the part that it overlaps the beginning of down time and then add that amount to the end of the scheduled down time. I'm pretty sure I can figure it out but if there were a function similar to the networkdays that would exclude times instead of dates that woluld be ideal "David" wrote: Hi, The set up on this is important, but try this: RunTime StartDate/Time DownTime FinishDate/Time 1.16666 4/18/05 6:00 AM 1/0/00 4:00 AM 4/19/05 2:00 PM =28/24 =4/24 =+B2+A2+C2 Format is date and Time Format is date and Time ColA ColB ColC ColD 28 hrs/24 4hrs/24 Thanks, "Paulymon" wrote: Is there a function similar to Networkday for time? That would exclude a time range from calculations? I need to compute the start and end time of an order. I know how long the job will take and the initial start time but the availability of the press is not 24/7 so I have a couple of problems. Let's say the job goes to press at 6am Monday and is scheduled for 28 hours of production. If the press ran 24/7 then the end time/date would be Tuesday at 10am. Unfortunately the press is down from 4pm to 8pm on Monday and so will not be done until Tuesday at 2pm. I'm generating data for multiple orders over several days and the press availability time may shift from day to day. So the end time of a job cannot land during one of the down periods or take account the downtime periods when figuring the end time of an order. Any help would be appreciated--even if it's just pointing me in the right direction. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding times but excluding a time range
Hi,
Maybe I am missing something StartTime + DownTime + RunTime should equal TotalTime. Maybe a simple one, not crossing dates will help me understand. Run Time 3 hrs, DownTime 1 hour, total elapsed time 4 hours. Start at Noon (12 pm) Work 1 hour ( it is 1 pm now) - 1 Down for 1 hour (it is now 2 pm) -1 Work for 2 hours (it is now 4 pm) -2 Total = 4 Am I missing something here? Thanks, "Paulymon" wrote: Thanks for your response. What I'm having a problem with is what happens when the start time + the order run time lands in the middle of the down time. It would have to be adjusted for the part that it overlaps the beginning of down time and then add that amount to the end of the scheduled down time. I'm pretty sure I can figure it out but if there were a function similar to the networkdays that would exclude times instead of dates that woluld be ideal "David" wrote: Hi, The set up on this is important, but try this: RunTime StartDate/Time DownTime FinishDate/Time 1.16666 4/18/05 6:00 AM 1/0/00 4:00 AM 4/19/05 2:00 PM =28/24 =4/24 =+B2+A2+C2 Format is date and Time Format is date and Time ColA ColB ColC ColD 28 hrs/24 4hrs/24 Thanks, "Paulymon" wrote: Is there a function similar to Networkday for time? That would exclude a time range from calculations? I need to compute the start and end time of an order. I know how long the job will take and the initial start time but the availability of the press is not 24/7 so I have a couple of problems. Let's say the job goes to press at 6am Monday and is scheduled for 28 hours of production. If the press ran 24/7 then the end time/date would be Tuesday at 10am. Unfortunately the press is down from 4pm to 8pm on Monday and so will not be done until Tuesday at 2pm. I'm generating data for multiple orders over several days and the press availability time may shift from day to day. So the end time of a job cannot land during one of the down periods or take account the downtime periods when figuring the end time of an order. Any help would be appreciated--even if it's just pointing me in the right direction. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding times but excluding a time range
Thanks for your replies.
I have a press that has 2 shifts currently. 6am-4pm and 8pm-6am I need to be able to project the end time of a job while including these variables. E.g.: Job goes to press Monday at 6am and is scheduled for 16 hours. so the projected end time would be 10pm but because we have the gap in shifts it would actually be 2am the following day. I would like to be able to enter the start date in one cell and have it come up with the projected end date & Time I would have an array that gave the time ranges that the press wasn't available. like Day St.Downtime End.Downtime Mon 16:30 20:30 Tue 16:30 20:30 Wed 16:30 20:30 Thu 16:30 20:30 Fri 16:30 20:30 Sat 16:30 20:30 Sun 16:30 20:30 and the formula would look at this when deciding what the projected end date/time would be. "David" wrote: Hi, Maybe I am missing something StartTime + DownTime + RunTime should equal TotalTime. Maybe a simple one, not crossing dates will help me understand. Run Time 3 hrs, DownTime 1 hour, total elapsed time 4 hours. Start at Noon (12 pm) Work 1 hour ( it is 1 pm now) - 1 Down for 1 hour (it is now 2 pm) -1 Work for 2 hours (it is now 4 pm) -2 Total = 4 Am I missing something here? Thanks, "Paulymon" wrote: Thanks for your response. What I'm having a problem with is what happens when the start time + the order run time lands in the middle of the down time. It would have to be adjusted for the part that it overlaps the beginning of down time and then add that amount to the end of the scheduled down time. I'm pretty sure I can figure it out but if there were a function similar to the networkdays that would exclude times instead of dates that woluld be ideal "David" wrote: Hi, The set up on this is important, but try this: RunTime StartDate/Time DownTime FinishDate/Time 1.16666 4/18/05 6:00 AM 1/0/00 4:00 AM 4/19/05 2:00 PM =28/24 =4/24 =+B2+A2+C2 Format is date and Time Format is date and Time ColA ColB ColC ColD 28 hrs/24 4hrs/24 Thanks, "Paulymon" wrote: Is there a function similar to Networkday for time? That would exclude a time range from calculations? I need to compute the start and end time of an order. I know how long the job will take and the initial start time but the availability of the press is not 24/7 so I have a couple of problems. Let's say the job goes to press at 6am Monday and is scheduled for 28 hours of production. If the press ran 24/7 then the end time/date would be Tuesday at 10am. Unfortunately the press is down from 4pm to 8pm on Monday and so will not be done until Tuesday at 2pm. I'm generating data for multiple orders over several days and the press availability time may shift from day to day. So the end time of a job cannot land during one of the down periods or take account the downtime periods when figuring the end time of an order. Any help would be appreciated--even if it's just pointing me in the right direction. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding times but excluding a time range
Hi,
The gap would never overlap but the shift times might change from day to day e.g. Mon may be 6a-4p & 8p to 6a but tue might be 6a-2p & 8p to 6a which is why I put the time ranges in seperate cells and had times for each individual day. Thanks Paul "David" wrote: Hi Again, This is clearer, but I have one more question. The down time will always occur between shifts and there are times it will NOT overlap into the 2nd shift or will it always overlap? In the example you have all the downs times exactly the same, it this always going to be the case? In no cases will the downtime break into the first shift? We might be able to do this better via e mail, mine is . Thanks, "Paulymon" wrote: Thanks for your replies. I have a press that has 2 shifts currently. 6am-4pm and 8pm-6am I need to be able to project the end time of a job while including these variables. E.g.: Job goes to press Monday at 6am and is scheduled for 16 hours. so the projected end time would be 10pm but because we have the gap in shifts it would actually be 2am the following day. I would like to be able to enter the start date in one cell and have it come up with the projected end date & Time I would have an array that gave the time ranges that the press wasn't available. like Day St.Downtime End.Downtime Mon 16:30 20:30 Tue 16:30 20:30 Wed 16:30 20:30 Thu 16:30 20:30 Fri 16:30 20:30 Sat 16:30 20:30 Sun 16:30 20:30 and the formula would look at this when deciding what the projected end date/time would be. "David" wrote: Hi, Maybe I am missing something StartTime + DownTime + RunTime should equal TotalTime. Maybe a simple one, not crossing dates will help me understand. Run Time 3 hrs, DownTime 1 hour, total elapsed time 4 hours. Start at Noon (12 pm) Work 1 hour ( it is 1 pm now) - 1 Down for 1 hour (it is now 2 pm) -1 Work for 2 hours (it is now 4 pm) -2 Total = 4 Am I missing something here? Thanks, "Paulymon" wrote: Thanks for your response. What I'm having a problem with is what happens when the start time + the order run time lands in the middle of the down time. It would have to be adjusted for the part that it overlaps the beginning of down time and then add that amount to the end of the scheduled down time. I'm pretty sure I can figure it out but if there were a function similar to the networkdays that would exclude times instead of dates that woluld be ideal "David" wrote: Hi, The set up on this is important, but try this: RunTime StartDate/Time DownTime FinishDate/Time 1.16666 4/18/05 6:00 AM 1/0/00 4:00 AM 4/19/05 2:00 PM =28/24 =4/24 =+B2+A2+C2 Format is date and Time Format is date and Time ColA ColB ColC ColD 28 hrs/24 4hrs/24 Thanks, "Paulymon" wrote: Is there a function similar to Networkday for time? That would exclude a time range from calculations? I need to compute the start and end time of an order. I know how long the job will take and the initial start time but the availability of the press is not 24/7 so I have a couple of problems. Let's say the job goes to press at 6am Monday and is scheduled for 28 hours of production. If the press ran 24/7 then the end time/date would be Tuesday at 10am. Unfortunately the press is down from 4pm to 8pm on Monday and so will not be done until Tuesday at 2pm. I'm generating data for multiple orders over several days and the press availability time may shift from day to day. So the end time of a job cannot land during one of the down periods or take account the downtime periods when figuring the end time of an order. Any help would be appreciated--even if it's just pointing me in the right direction. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding times but excluding a time range
Hi,
I think I have something that will work for an individual job, I tested it on 3 senarios, but it is not set up for scheduling future jobs. I used the data you sent on two posts ago. All are set up to start Monday-4/25/05 at 6:00ambut down times are varied for testing: Job1 4:30 pm to 8:00 pm Job2 4:30 pm to 8:30 pm(Overlap into shift 2, as stated in prior post) Job3 4:30 pm to 8:30 pm 1rst Shift Times: Job1 6:00 am to 4:00 pm Job2 6:00 am to 4:00 pm Job3 6:00 am to 2:00 pm(This did not matter, since there was sufficient time in both shifts to finish job, even with down time) Send me you e mail, via my e ), I will send you the file. As I said, this does not send you schedule a second job, this analysis is of a single job under different conditions. Thanks, "Paulymon" wrote: Hi, The gap would never overlap but the shift times might change from day to day e.g. Mon may be 6a-4p & 8p to 6a but tue might be 6a-2p & 8p to 6a which is why I put the time ranges in seperate cells and had times for each individual day. Thanks Paul "David" wrote: Hi Again, This is clearer, but I have one more question. The down time will always occur between shifts and there are times it will NOT overlap into the 2nd shift or will it always overlap? In the example you have all the downs times exactly the same, it this always going to be the case? In no cases will the downtime break into the first shift? We might be able to do this better via e mail, mine is . Thanks, "Paulymon" wrote: Thanks for your replies. I have a press that has 2 shifts currently. 6am-4pm and 8pm-6am I need to be able to project the end time of a job while including these variables. E.g.: Job goes to press Monday at 6am and is scheduled for 16 hours. so the projected end time would be 10pm but because we have the gap in shifts it would actually be 2am the following day. I would like to be able to enter the start date in one cell and have it come up with the projected end date & Time I would have an array that gave the time ranges that the press wasn't available. like Day St.Downtime End.Downtime Mon 16:30 20:30 Tue 16:30 20:30 Wed 16:30 20:30 Thu 16:30 20:30 Fri 16:30 20:30 Sat 16:30 20:30 Sun 16:30 20:30 and the formula would look at this when deciding what the projected end date/time would be. "David" wrote: Hi, Maybe I am missing something StartTime + DownTime + RunTime should equal TotalTime. Maybe a simple one, not crossing dates will help me understand. Run Time 3 hrs, DownTime 1 hour, total elapsed time 4 hours. Start at Noon (12 pm) Work 1 hour ( it is 1 pm now) - 1 Down for 1 hour (it is now 2 pm) -1 Work for 2 hours (it is now 4 pm) -2 Total = 4 Am I missing something here? Thanks, "Paulymon" wrote: Thanks for your response. What I'm having a problem with is what happens when the start time + the order run time lands in the middle of the down time. It would have to be adjusted for the part that it overlaps the beginning of down time and then add that amount to the end of the scheduled down time. I'm pretty sure I can figure it out but if there were a function similar to the networkdays that would exclude times instead of dates that woluld be ideal "David" wrote: Hi, The set up on this is important, but try this: RunTime StartDate/Time DownTime FinishDate/Time 1.16666 4/18/05 6:00 AM 1/0/00 4:00 AM 4/19/05 2:00 PM =28/24 =4/24 =+B2+A2+C2 Format is date and Time Format is date and Time ColA ColB ColC ColD 28 hrs/24 4hrs/24 Thanks, "Paulymon" wrote: Is there a function similar to Networkday for time? That would exclude a time range from calculations? I need to compute the start and end time of an order. I know how long the job will take and the initial start time but the availability of the press is not 24/7 so I have a couple of problems. Let's say the job goes to press at 6am Monday and is scheduled for 28 hours of production. If the press ran 24/7 then the end time/date would be Tuesday at 10am. Unfortunately the press is down from 4pm to 8pm on Monday and so will not be done until Tuesday at 2pm. I'm generating data for multiple orders over several days and the press availability time may shift from day to day. So the end time of a job cannot land during one of the down periods or take account the downtime periods when figuring the end time of an order. Any help would be appreciated--even if it's just pointing me in the right direction. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calculate the total overlapping time of multiple tasks, excluding non-working times | Excel Worksheet Functions | |||
Calculating Turn Around Times excluding Sunday | Excel Worksheet Functions | |||
Adding Time from Name Range | Excel Discussion (Misc queries) | |||
adding up times x occurs, excluding if y repeats in a different co | Excel Discussion (Misc queries) | |||
Calculate number of hours between dates and times excluding Weekends | Excel Discussion (Misc queries) |