Scheduling in Excel (How to exclude non-working time in the calculation)

**mbrillian** · 09-03-2018, 10:54 AM

Good Evening,

Gonna need your help so badly, I am stuck with this case for quite long time.

So, basically, what I want to do is:
I want to know the completion time of one particular job, simply by adding the "start time" and "Process time", however, there is non-working hours that cannot execute the job:
a. Start day is 7:30 , end time is 23:30
b. 9:30-10:00 they need to have 1st break
c. lunch at 11:00 - 11:30
d. The second break is 13:30-14:00.
e. Dinner at 17:00-17:30
f. 1st break for second shift in 19.30-20:00
g. 2nd break for the second shift 21:30-22:00
g. Start day is 7:30 , end time is 23:30

For example, the first job started at 7:30, and takes 3 hours for the processing time, if there is no break time, then it will be finish at 10:30. However, as there is a break time between it, it will be finish at 11:00.

Using the same start time, but process time 3:30, then it will be finish at 12:00, instead of 11:30

I also attach the excel.

The first sheet is the scheduling area and the second sheet is the place I place the non-working hour.
nb: The next row of start time will be filled with the result of completion time of previous row.

THANKS !!!!

**Keebellah** · 09-04-2018, 02:57 AM

Hi, welcome to the forum.
I haven't looked at your file yet but the first thing I would do (I do it with my schedules) is set the break times in Named Ranges and include these in the calculations.
I assume you also consider weekends and holidays? In this case use the excel function NETWORKDAYS() which filter out the weekends and as an extra parameter you can include a list of holiday dates to exclude too.
Suggest you read up on working with dates and time values. If you Google this you'll find a lot of good tips and scripts.
I'll take a look later but this is to give you a push in my direction of thought

**mbrillian** · 09-06-2018, 04:16 AM

Hi Hans,

Thanks for your welcoming greeting.

As you suggested, I have tried several times, but it still unclear. The last effort only bring me to pass the first break time. For example if the start time is 9:00 and process time is 45 minutes, then the outcome is 10:15 instead of 9:45, but if the process time pass the second break time, then I am doomed...again.

Looking forward for your guidance.... Thankss

**Glenn Kennedy** · 09-06-2018, 04:19 AM

Please amend your profile to show your location. Regional settings can affect answers. "World" is not very helpful in determining that!!

**mbrillian** · 09-06-2018, 04:25 AM

Originally Posted by Glenn Kennedy

Please amend your profile to show your location. Regional settings can affect answers. "World" is not very helpful in determining that!!

Hi Glen, thanks for your information. I will change it right away. Btw, can you help me with this issue? Thankss

**Keebellah** · 09-06-2018, 04:26 AM

Once you 'settle' in the World ...

I'll check it out, was a little busy but hope to get back to you soon.

**Keebellah** · 09-06-2018, 09:28 AM

This is the way I would approach it. I had to remove the ocx compound you inserted (I guess for a calendar picker), hate those things and I kept getting the error.
I would use named ranges (see the file returned)
top left the breaks and the small grey table with the the way I did it. Works until after the second break of the first shift.

There are some thing I would consider (if it were me doing this), like what is the first shift start (07:30) and end and when does the second shift start and end (23:30)

If you look at the grey table:

In N1 you enter the start time, N2 the end time, the total below is the total in P the different times off and the total below, in between the net time with this idea and embedded formulas you could use if for all

Hope that once you look at it it will make sense, it's easier to do than explain