Gantt Chart time flowing over days with lunch breaks and 8 hour workday

[MelMel]

I think I may have bit off more than I can chew. I said that I could do this and don't think I can now after playing with it for 3 days. I need to create a chart (assuming Gantt would be best) to auto shade a certain number of cells. We have complicated calculations to give the minutes needed to complete each part of a job. I need to take those minutes and convert them to a chart.
I think my first problem is that I need to show the estimated completion date for each part of the job (start and end time in minutes), allowing for lunch and an 8 hour workday. When I enter a formula using the workday function (=WORKDAY(A3,C3-1,$A$22:$A$27) It doesn't allow for the time of day or the lunch break. Many of the parts are multi-day - they will take at least 2 days. But there are some areas that may only take a few hours. Most of the jobs are over about a week's time. That's the first problem.
Then I need to create the chart with conditional formatting. Everything I am seeing is either VBA (which I've never done) or only within one day/week.
I have tons of data and can put it in a variety of places, so I don't think I need to give the cell references or layout at this point, because I think it may need to change.
PLEASE HELP!!

 

[kgfworld]

Can you post an example of what you have?

 

[MelMel]

This isn't ideal. I can change the layout if it would be easier to do the formula. I need to take the Equiv. Hours or the Est Taken time and shade for each after a set start time. So it would start at say 10 am, the first block would be one color and go for 15 hours (excluding lunch). Then the next area would start in directly following in another color for 16 hours.
If that format doesn't work, I could simplify and just have a start day/time for each area and calculate what the estimated completion day/time would be. I like the simplicity of this, but I can't get it to work because of the lunch hours using WORKDAY function. That is the second image below.

1

2

3

4

5

6

7

8

9

10

11

BP

6.1

STEP:

1st notice of loss

ARRIVAL

CHECK IN

BP

PARTS

RFD

BODY

PAINT

BUILD

FINAL

Claim Paid

Body

9.2

Est. Takt:

0

1

60

64

0

175

90

60

4

Paint

4.7

Equiv Hrs:

0

0.25

15

16

0

43.75

22.5

15

1

Build

6.1

15:00

Thurs

--FRIDAY--

Mon

Tues

Wed

Thurs

--FRIDAY--

Mon

Tues

Wed

Thurs

--FRIDAY--

Mon

Tues

CONDITION

5/10/12

5/11/12

5/14/12

5/15/12

5/16/12

5/17/12

5/18/12

5/21/12

5/22/12

5/23/12

5/24/12

5/25/12

5/28/12

5/29/12

7:00 AM

7:15 AM

7:30 AM

7:45 AM

8:00 AM

8:15 AM

8:30 AM

8:45 AM

9:00 AM

9:15 AM

9:30 AM

9:45 AM

10:00 AM

10:15 AM

10:30 AM

10:45 AM

11:00 AM

11:15 AM

11:30 AM

11:45 AM

12:00 PM

12:15 PM

12:30 PM

12:45 PM

1:00 PM

1:15 PM

1:30 PM

1:45 PM

2:00 PM

2:15 PM

2:30 PM

2:45 PM

3:00 PM

3:15 PM

3:30 PM

3:45 PM

4:00 PM

4:15 PM

4:30 PM

4:45 PM

5:00 PM

5:15 PM

5:30 PM

<colgroup><col><col><col><col><col span="11"><col><col></colgroup><tbody>
</tbody>

Start Time	Hours	Days	end date
January 10, 2013	16	2	#VALUE!

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>

 

[MelMel]

Is it possible to write a formula to add 60 "Blocks of time" (each block of time = 15 minutes) to a day/time and find the completion date?