gantt chart production plan with conditional format

%3CLINGO-SUB%20id%3D%22lingo-sub-1482750%22%20slang%3D%22en-US%22%3Egantt%20chart%20production%20plan%20with%20conditional%20format%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1482750%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eattached%20is%20the%20my%20production%20plan%20file%20with%20conditional%20formats.%20each%20day%20have%20two%20shifts.%20i%20have%20mention%20order%20qty%20here%20and%20capacity%20for%20a%20day.%20i%20used%20two%20merge%20cells%20to%20highlight%20gantt%20chart%20to%20show%20production%20plan%20and%20actual%20production.%20but%20if%20i%20have%20order%20less%20than%20capacity%20i%20need%20to%20highlight%20one%20shift%20for%20a%20day%20as%20per%20second%20line%20and%20need%20to%20highlight%20complete%20status%20as%20same%20with%20highlight%20one%20cell.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eanyone%20can%20help%20to%20me%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1482750%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EAdmin%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ECharting%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EDeveloper%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20for%20web%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EPower%20BI%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1483838%22%20slang%3D%22en-US%22%3ERe%3A%20gantt%20chart%20production%20plan%20with%20conditional%20format%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1483838%22%20slang%3D%22en-US%22%3Efirst%20I%20would%20unmerge%20all%20those%20cells%20(I%20hate%20merged%20cells%20in%20general%20as%20then%20prevent%20a%20lot%20functions).%20Next%20I%20would%20change%20End%20date%20to%20calculate%20Expected%20end%20date%20(including%201%2F2%20days).%20You%20might%20want%20to%20use%20the%20previous%20jobs%20expected%20end%20date%20as%20the%20next%20row's%20start%20date%3F%20But%20then%20you%20should%20be%20able%20to%20use%20a%20conditional%20formatting%20formula%20accordingly%20based%20on%20the%20expected%20end%20date.%20For%20example%20you%20can%20use%20AVG(M%244%3AO%244)%20to%20give%20you%20day%206%20for%20Day%20shift%20but%20then%206.5%20for%20Night%20shift.%20Hope%20that%20helps.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1485083%22%20slang%3D%22en-US%22%3ERe%3A%20gantt%20chart%20production%20plan%20with%20conditional%20format%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1485083%22%20slang%3D%22en-US%22%3EMany%20thanks%20for%20the%20quick%20respond!%20i've%20tried%20this%20and%20something%20complicated.%20can%20you%20please%20help%20to%20do%20this%20by%20excel%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1485875%22%20slang%3D%22en-US%22%3ERe%3A%20gantt%20chart%20production%20plan%20with%20conditional%20format%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1485875%22%20slang%3D%22en-US%22%3Ewell%20I%20guess%20I%20don't%20understand%20what%20you%20are%20trying%20to%20do%20then.%20Maybe%20you%20can%20show%20a%20couple%20of%20example%20of%20what%20you%20are%20trying%20to%20achieve.%20and%20point%20out%20the%20examples%20that%20the%20formulas%20don't%20work%20for%20you.%3C%2FLINGO-BODY%3E
Highlighted
Contributor

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?

 

3 Replies
Highlighted
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.
Highlighted
Many thanks for the quick respond! i've tried this and something complicated. can you please help to do this by excel
Highlighted
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.