Utilising only part of the 24hrs within an excel day

[mikemetson]

Help,
I am trying to plan a production schedule using excel. In a typical working day there are 10 useful hours, and 4 useful hours on a saturday. If I plan my production tasks by the hour, how can I get excel to roll over the date into the next availible working day once the jobs in hand last beyond the hours alloted for that particular day?

eg
10/01/06
job 1 = 9 hours
job 2 =6 hours

Therefore 15 hours in total

tuesday = 10 hours
wednesday = 10 hours

therefore job 2 starts on day 1 and finishes on day 2.


working day lasts from 06.30-16.30

Kind Regards

Mike

 

[Gavona]

Tricky.
I can create a results sheet looking like this:

Job	Job	All Jobs	Finish Day	Finish Time
Ref	Time	to Date		Decimal Hrs
				
Job1	15	15	Fri 13-Jan-06	 11.50 
Job2	2	17	Fri 13-Jan-06	 13.50 
Job3	3	20	Sat 14-Jan-06	 6.50 
Job4	7	27	Mon 16-Jan-06	 8.50 
Job5	24	51	Wed 18-Jan-06	 12.50 
Job6	1	52	Wed 18-Jan-06	 13.50 
Job7	1	53	Wed 18-Jan-06	 14.50 
Job8	3	56	Thu 19-Jan-06	 7.50 
Job9	4	60	Thu 19-Jan-06	 11.50 
Job10	3	63	Thu 19-Jan-06	 14.50 
Job11	20	83	Mon 23-Jan-06	 9.50 
Job12	3	86	Mon 23-Jan-06	 12.50 
Job13	4	90	Tue 24-Jan-06	 6.50 
Job14	4	94	Tue 24-Jan-06	 10.50

It requires to create (best on a separate sheet) a table like this:

Thu 12-Jan-06	 6.50 	0	Thu 12-Jan-06
Fri 13-Jan-06	 6.50 	10	Fri 13-Jan-06
Sat 14-Jan-06	 6.50 	20	Sat 14-Jan-06
Sun 15-Jan-06	 6.50 	25	Sun 15-Jan-06
Mon 16-Jan-06	 6.50 	25	Mon 16-Jan-06
Tue 17-Jan-06	 6.50 	35	Tue 17-Jan-06
Wed 18-Jan-06	 6.50 	45	Wed 18-Jan-06
Thu 19-Jan-06	 6.50 	55	Thu 19-Jan-06
Fri 20-Jan-06	 6.50 	65	Fri 20-Jan-06
Sat 21-Jan-06	 6.50 	75	Sat 21-Jan-06
Sun 22-Jan-06	 6.50 	80	Sun 22-Jan-06
Mon 23-Jan-06	 6.50 	80	Mon 23-Jan-06
Tue 24-Jan-06	 6.50 	90	Tue 24-Jan-06
Wed 25-Jan-06	 6.50 	100	Wed 25-Jan-06

The 2nd column is the time each day starts in decimal hours
The third is the "Cumulative Hours at start of day"

Create a named range encompassing the last 2 columns and call it "DayLook"
Create a named Range encompassing all the columns and call it "TimeLook"

The formulae for "Finish Day" in cell E5 of your results sheet is:
=VLOOKUP(C5,DayLook,2,1)
The formula for "FinishTime" is:
=C5-VLOOKUP(D5,TimeLook,3,0)+VLOOKUP(D5,TimeLook,2,0)

Not sure if this precisely meets your needs but it should help.


Gavin