Production plan, adding breaks to end time

[MckBen91]

Hi all,

The problem im having seems quite simple but im not sure how to solve.

I've created a spreadsheet where it calculates a long list of jobs of the machine and provides me with an end time for each job. But im having to add (with a check box) 15 or 30 minutes at particular times of the day (08:00/10:00/12:00/14:00/16:00) for the breaks. Very hopeful that there could be a formula or solution.

E.g. If a jobs starts at 06:00 and would take 3hrs, I would need it to add 15 minutes for me to take the break into account (09:15).


Any help with this would be greatly appreciated!!

Kind regards,

B.

 

[NoSparks]

Here's a suggestion

The green cells would amount to your file of post 5
The orange cells would be formula to see if previous projected end time is greater than the break time and add the break minutes if it is.

 

[MckBen91]

Hi NoSparks,

Thank you.

I did try this but not sure if I'm doing it right?

The 1st row does add the breaks how it should, but then when we get to the 2nd row it will then start adding breaks from 06:00 and not take into account for the end time from the above row. (End row finishes @ 15:00 but 2nd row will start adding breaks from 06:00 as end time is greater).

Any idea what formula I could use to avoid this?

 

[NoSparks]

The break time has to be both
---> greater than or equal to the start time
and
---> less than the projected end time

in words
=if(and(breaktime>=starttime, breaktime<previousprojectedtime), previousprojectedtime+breakminutes, previousprojectedtime)

 

[MckBen91]

The break time has to be both
---> greater than or equal to the start time
and
---> less than the projected end time

in words
=if(and(breaktime>=starttime, breaktime<previousprojectedtime), previousprojectedtime+breakminutes, previousprojectedtime)

Reading your above formula looks as if it should be fine but have this and it doesn't seem to be working either. Have copied a link to see of you can spot where I'm going wrong?

Adding Breaks.xlsx | Powered by Box

app.box.com

 

[MckBen91]

As when i enter the above formula it doesn't seem to add the break time? Even though it should then be adding 06:00 & 08:00 breaks.

 

[NoSparks]

Seeing you didn't use the columns between the breaks, eliminate them.

Hopefully your other question has sorted out the requirements for the break times to include the date part even though they don't have to be displayed.

There are 10 different possible relationships between the start time, projected time, start of break and end of break times.

start < start of break and projected < start of break​

start < start of break and projected = start of break​

start < start of break and projected > start of break and < end of break​

start < start of break and projected = end of break​

start < start of break and projected > end of break​

start= start of break and projected between start and end of break​

start= start of break and projected = end of break​

start= start of break and projected > end of break​

start = end of break and projected > end of break​

start > end of break and projected > end of break​

some need the break time added to the projected time and some don't​

Being no good with formulas I don't know if this covers all the bases or not,
but try this in E5
=IF($B5<=E$3+E$4,IF(D5>=E$3,D5+E$4,D5),D5)
drag it across to the last break time.

If that doesn't work I'm afraid I can't help you.

 

[MckBen91]

Hi, currently on night shifts at the moment so haven't been able to apply what you've said on your last comment. What you said.about the break times and the date being displayed worked a treat though!! Thank you greatly for that!! People like you are what makes this site amazing!!

 

[MckBen91]

Hi, just want to say a massive thank you!! This has solved my problem!! Got there in the end