gantt chart production plan with conditional format

[Ravindu94]

Hi,

attached is the my production plan file with conditional formats. each day have two shifts. i have mention order qty here and capacity for a day. i used two merge cells to highlight gantt chart to show production plan and actual production. but if i have order less than capacity i need to highlight one shift for a day as per second line and need to highlight complete status as same with highlight one cell.

anyone can help to me?

[#2]

first I would unmerge all those cells (I hate merged cells in general as then prevent a lot functions). Next I would change End date to calculate Expected end date (including 1/2 days). You might want to use the previous jobs expected end date as the next row's start date? But then you should be able to use a conditional formatting formula accordingly based on the expected end date. For example you can use AVG(M$4:O$4) to give you day 6 for Day shift but then 6.5 for Night shift. Hope that helps.

[#3]

Many thanks for the quick respond! i've tried this and something complicated. can you please help to do this by excel

[#4]

well I guess I don't understand what you are trying to do then. Maybe you can show a couple of example of what you are trying to achieve. and point out the examples that the formulas don't work for you.