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.

 

[bebo021999]

Like this?

Book3.xlsx
	A	B	C	D	E	F
1	Start	# hours	End time		Break time (minute)	15
2	06:00	3	09:15
3	09:00	7	17:00
Sheet3

Cell Formulas
Range	Formula
C2:C3	C2	=A2+B2/24+SUMPRODUCT(--(A2+B2/24>{8,10,12,14,16}/24))*$F$1/1440

 

[MckBen91]

Hi,

Massively appreciate your help with this!

Not sure if its entirely what I mean though.

How does this formula work (Newbie to formulas, learning and absorbing all that I can though)

The formula seems to work, but then if I change A3 to 09:15 (as that's when the next job would start) the new end time shows 17:30. Should be showing 17:15 as there has only been 4 breaks at 15 minutes?

Also would it be possible to alternate between 15 minutes and 30 minutes?

08:00/12:00/1600/18:00/20:00/00:00/04:00/06:00, are all 15 minute breaks.

10:00/14:00/22:00/02:00, are all 30 minutes.


Hope this makes sense and again appreciate your help!!


Kind regards,

B

 

[bebo021999]

Try to mock up your desired result for each: 15 and 30.
Give full cases of start, hours and end time, (especially working pass mid night, if any)
Try in XL2BB mini sheet.

 

[MckBen91]

Hi,

In my last comment, 18:00 was suppose to be in the 30min break section.

Box (Tried using the XL2BB but no luck with it) Have copied a link from DropBox.com though. This is the spreadsheet that I am currently using.

If the break times could be added to the "Total run time" or in another Row so I can add to "Total run time". As I have other variables that add to the "Total run time"

An example of this:

Job 1 takes 3 hours to complete (starts at 09:00) it will finish at 12:45. 30min break at 10:00 and 15min break at 12:00.

Job 2 would then start at 12:45 and takes 2 hours to complete. This job would finish 15:15. 30min break at 14:00

Job 3 would start at 15:15 and takes 6 hours to complete. This job would finish at 22:45. 15min break at 16:00, 30min break at 18:00, 15min break at 20:00 and 30min break at 22:00.


Kind regards,

B

 

[bebo021999]

Which range of start hour could be? I mean, is it always after 6 AM, before 18 PM, or any time?
Also working hours, could it be >24h? or alway <24h? <36h?

 

[MckBen91]

Which range of start hour could be? I mean, is it always after 6 AM, before 18 PM, or any time?
Also working hours, could it be >24h? or alway <24h? <36h?

So its a 24/7 site. Some jobs will take 30mins but the next could take 36hrs.

1 shift will start at 06:00 but we allow them 30mins to do there handover etc, we expect them to start the machine up at 06:30. Finishing time is at 18:00 (12hr shift). And its the same for the night shift. 18:00 (18:30) till 06:00.

A job may only be half finished at the end of shift. E.g. Starts at 16:30, take 3 hrs to run. End time will be 20:00, 30min break at 18:00.

Hope this answers your question

 

[MckBen91]

Hi Bebo, are you getting anywhere with this??

 

[bebo021999]

Without XL2BB, its very hard to imagin what it is. Try it again.
Or an image, at least.

 

[MckBen91]

Without XL2BB, its very hard to imagin what it is. Try it again.
Or an image, at least.

Something similar to this, will need to be able to add to the 'minutes' column if there is additional time to be added (set up time or reduced run speed).