Timeline - Calculate end date/time with shift/lunch start and end

I have been trying to create a workbook that will generate a timeline for manufacturing when entering a start date and time, job quantity, and operation times. This timeline would take into account shift start and end times, lunch start and end times, as well as a holiday list. I ran into issues getting the End Date to properly take into account the shift times, and really ran into problems trying to include lunch breaks (I tried using networkdays, but ran into issues when an operation started after lunch one day, and ended prior to lunch on another day, or other combinations).

My brain is currently fried from trying to figure this out, so please let me know if anything needs to be clarified.

Regards,

Mike

Let us try to make it 1 at a time to clarify each issue


So what is your first issue after entering Start Date & Start Time?


Thanks
Rev12

See if this helps.

Best approach to this kind of things seems to be in 3 parts, remainder of start day, whole days, and what is left to do on the end day.

I've assumed that some of the columns in your table were just there to assist with the calculation process. With this in mind, I've removed some that I didn't see as necessary and added a few more.

The section with red borders can be hidden for aesthetic purposes if desired. I've commented the headers to show the purpose of each formula.

Rev12,

The first issue would be that the End Date w/o lunch break column is not calculating correctly, given the shift start and end times. If you review the attached, given the inputs on Timeline tab, the end time of 2:30 pm is correct. However, adding another 30 minutes to the op total time rolls the end date to 6/12 7:00 am, as opposed to 6/11 3:00 pm.

jason.b75,

Thank you for the reply. Time in Excel always seems to be hard for my to wrap my brain around. A lot of columns were helper columns, and I have no issues with them being removed.

I tried throwing in some data to see what results I would get with your spreadsheet, and found some issues:

An operation that would start and finish on the same day is not calculating correctly (see Timeline (2) of the attached)
A start time of 7:00 am would generate #N/A errors (see Timeline (3) of the attached)

This formula in G5 will fix the issue for start and finish on the same day.

=IF(C5="","",IF(SUM(J5:K5)=0,F5+H5+IF(AND(MOD(F5,1)<$B$17,MOD(F5,1)+H5>$B$17),$B$18-$B$17),$B$16+WORKDAY(F5,I5+(E5>H5),Holidays!$B$3:$B$37)+(IF(K5<=($B$17-$B$16),K5,K5+($B$18-$B$17)))))

And this one in H5 will fix the errors with a 07:00 start time.

=IF(C5="","",IF(IFERROR(MATCH(MOD(F5,1),$B$16:$B$19)=1,TRUE),MIN($B$17-MOD(F5,1)+$B$19-$B$18,E5),MIN($B$19-MOD(F5,1),E5)))

Both need to be filled down to row 14 after entry.

I think they are right this time, but have only done a quick 5 minute test.

jason.b75,

These changes seemed to fix all issues. The formula you posted for G5 could not be filled to row 14 as the formula does not reference column G in the row above, so I left it alone.

All calculations are using the op total time in column E, correct?

The only other nitpicky thing I see at this point is that operations can start at either the shift end or lunch start times. Would it be difficult to force these to start at the start of the following shift or at the end of lunch? If it is difficult, it is something I could live without.

Thank you so much for your help jason.b75!

Just a quick reply for now, I'm wrong side of the Atlantic to still be awake

The formula in G5 can be filled down, it doesn't need to refernce the row above.

Start date in F5 is based on B2 & B5. Start date in F6 onwards is based on the end date of the previous row.

All end dates are based on the start date of the same row with adjustments made based on the helper columns. The helpers are calculated from total op time.

Due to an anomaly in the way excel calculates times, entering the start time as the start of lunch causes it to correct automatically, for the first row at least. The cause of this could make it a bit hit and miss, I'll have a look in the morning at ways to make it more consistent, but it could end up over complicating the formula.

An updated copy of the test file including the revised formulae from post #6 and operations due to start at shift end or lunch start moved to next shift and after lunch respectively.

Timeline (3) is the updated sheet, the others still contain various errors from previous testing.

The only thing that I haven't done is added in any kind of tolerance, if applicable. For example, if an operation ends 1 minute before the end of a shift, should that minute be used to start the next op, or dropped and the new op started clean on the following shift? Similarly, if the op is due to finish 1 minute into the next shift, should the current shift finish time be delayed in order to finish the op?

If such a thing is required, what sort of tolerance should be set? 1 minute, 5 minutes, or variable?

Maybe I'm overthinking your potential requirements, I'm currently working on a similar project for my own employer, which has many such variables to try and incorporate.

What other things are you planning on incorporating? Things that I would like to add but are not necessary:

Efficiency %
Inspection Time
Queue Time (start of an operation would be x amount of time after the end of the previous operation)
Second shift
Some way to add an overlap of operations
Either tolerance on the start / end time, or some way to override for a specific operation

Things like inspection and queue time are simple to include as and when required by inserting some extra columns and adding them into the op total time.

Second shift is possible, I assume that it should all go into one table rather than separate tables for each shift.

For efficiency %, overlap and tolerance, some examples of what you're expecting would be helpful, it's easier to hit the target when there is one to aim for. If you set up your sample sheet to show what is needed, with expected results. Just type them in manually and highlight the cells to show that you need a formula to achieve those results.

Most of what I'm trying to do with my own project will not fit to what you need, I'm working with multiple stage operations and preset finish times, I'm trying to make reality fit theory when things don't go according to plan.

I've probably spent an hour at most on your file to get it to the current stage, I've been working on my own project on and off for a few months and made much less progress.