Trouble converting Gantt from Wkly to Mthly

LS0009

New Member
Joined
Jan 25, 2021
Messages
1
Office Version
  1. 365
Platform
  1. Windows
Hello,

Having a rough go at converting my Gantt from Weekly to Monthly visuals. Problem: Partial months are not being highlighted in the Gantt. In ex below, first milestone has actual finish on 10/21 - would like Month 5 to be highlighted. Would like this to work for both the "Scheduled" and "Actual" start/finish

Gantt_LS1.xlsx
BCDEFGHIJKLMNOPQRSTUV
4PROJECT START DATE06/01/21
5Month 1Month 2Month 3Month 4Month 5Month 6Month 7Month 8Month 9Month 10Month 11Month 12
6TASK TITLE Sub Task TitleTASK DESCRIPTION Sub Task DescriptionPRIORITY / RISK LEVEL Resource Type >>SCHEDULED STARTSCHEDULED FINISHACTUAL STARTACTUAL FINISHFINISH VARIANCEDURATION (DAYS)6/30/217/31/218/31/219/30/2110/31/2111/30/2112/31/211/31/222/28/223/31/224/30/225/31/22
7 *Set Priority / Risk06/01/2109/15/2106/01/2110/21/2126103ftrftrftrfntrentrentrentrentrentrentrentrentr
8 *Set Priority / Risk10/01/2111/01/2110/01/2112/15/213254entrentrentrentrftrfntrentrentrentrentrentrentr
Project Gantt
Cell Formulas
RangeFormula
K6K6=EOMONTH(C4,0)
L6:V6L6=EOMONTH(K6,1)
I7:I8I7=IF(AND(AND(NOT(ISBLANK(F7)),NOT(ISBLANK(H7))),F7<>H7),NETWORKDAYS(F7,H7)-1,"")
J7:J8J7=NETWORKDAYS(G7,H7)
K7:S8K7=IF(OR(AND(K$6<=$G7,K$6<=$E7,K$6<=$H7,K$6<=$F7),AND(K$6<=$G7,K$6>$E7,K$6<=$H7,K$6>$F7),AND(K$6>$G7,K$6<=$E7,K$6>$H7,K$6<=$F7),AND(K$6>$G7,K$6>$E7,K$6>$H7,K$6>$F7)),"entr",IF(OR(AND(K$6<=$G7,K$6>$E7,K$6<=$H7,K$6<=$F7),AND(K$6>$G7,K$6>$E7,K$6>$H7,K$6<=$F7)),"etr",IF(OR(AND(K$6>$G7,K$6<=$E7,K$6<=$H7,K$6<=$F7),AND(K$6>$G7,K$6>$E7,K$6<=$H7,K$6>$F7)),"fntr",IF(AND(K$6>$G7,K$6>$E7,K$6<=$H7,K$6<=$F7),"ftr","err"))))
T7:V8T7=IF(OR(AND(EDATE(T$6,1)<=$G7,EDATE(T$6,1)<=$E7,EDATE(T$6,1)<=$H7,EDATE(T$6,1)<=$F7),AND(EDATE(T$6,1)<=$G7,EDATE(T$6,1)>$E7,EDATE(T$6,1)<=$H7,EDATE(T$6,1)>$F7),AND(EDATE(T$6,1)>$G7,EDATE(T$6,1)<=$E7,EDATE(T$6,1)>$H7,EDATE(T$6,1)<=$F7),AND(EDATE(T$6,1)>$G7,EDATE(T$6,1)>$E7,EDATE(T$6,1)>$H7,EDATE(T$6,1)>$F7)),"entr",IF(OR(AND(EDATE(T$6,1)<=$G7,EDATE(T$6,1)>$E7,EDATE(T$6,1)<=$H7,EDATE(T$6,1)<=$F7),AND(EDATE(T$6,1)>$G7,EDATE(T$6,1)>$E7,EDATE(T$6,1)>$H7,EDATE(T$6,1)<=$F7)),"etr",IF(OR(AND(EDATE(T$6,1)>$G7,EDATE(T$6,1)<=$E7,EDATE(T$6,1)<=$H7,EDATE(T$6,1)<=$F7),AND(EDATE(T$6,1)>$G7,EDATE(T$6,1)>$E7,EDATE(T$6,1)<=$H7,EDATE(T$6,1)>$F7)),"fntr",IF(AND(EDATE(T$6,1)>$G7,EDATE(T$6,1)>$E7,EDATE(T$6,1)<=$H7,EDATE(T$6,1)<=$F7),"ftr","err"))))
B7:B8B7=IFERROR(VLOOKUP(#REF!,Milestones,3,FALSE),"")
Cells with Conditional Formatting
CellConditionCell FormatStop If True
D8Cell Valuecontains "HIGH"textNO
D8Cell Valuecontains "MEDIUM"textNO
D8Cell Valuecontains "LOW"textNO
D8Cell Valuecontains "EXTREME"textNO
D7Cell Valuecontains "HIGH"textNO
D7Cell Valuecontains "MEDIUM"textNO
D7Cell Valuecontains "LOW"textNO
D7Cell Valuecontains "EXTREME"textNO
K7:AH8Cell Value="err"textNO
K7:AH8Cell Value="entr"textNO
K7:AH8Cell Value="fntr"textNO
K7:AH8Cell Value="ftr"textNO
K7:AH8Cell Value="etr"textNO
Cells with Data Validation
CellAllowCriteria
D7:D8List=Priorityrisk
 

Some videos you may like

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

Watch MrExcel Video

Forum statistics

Threads
1,126,957
Messages
5,621,827
Members
415,859
Latest member
Vain

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top