Recurring tasks using Gantt Charts and Conditional Formatting

MarisaRaucci

New Member
Joined
Sep 21, 2021
Messages
7
Office Version
  1. 365
Platform
  1. Windows
Hi,
I am trying to come up with a formula to calculate biweekly dates in excel, using conditional formatting. Ideally I would like to be able to enter the start date and end date, day of week, and frequency, and identify the dates using an X, then a conditional format to shade the X using a specific colour. For example:


Mon 1 OctoberTue 2 Oct
Day of WeekFrequencyProject Start DateProject End Date
Status MeetingTuebi weekly1 October 20211 November 2021X

I would like to be able to set up a formula in in the above table to be able to capture when the biweekly meetings occur, and then place an X in the date it relates to. I am using Excel, and don't have access to Project Management, but I am quite sure that someone can come up with this formula.

thanks
Marisa
 

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
5,109
Office Version
  1. 365
Platform
  1. MacOS
you are welcome, glad to have helped

The file will only be on the share for a few days , and then removed , hence why i also included the XL2BB for anyone searching in the future will still be able to see the solution, even without the workbook
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

MarisaRaucci

New Member
Joined
Sep 21, 2021
Messages
7
Office Version
  1. 365
Platform
  1. Windows
Hi again, one question I forgot to ask, if I wanted to choose distinguish from the bi weekly and weekly meetings with a different colour, can this be done? I have used conditional formatting so that if there is an X, it use red, but now I'm thinking it would be great if I could use two different colours to do this.

Can this be achieved?

cheers
Marisa
 

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
5,109
Office Version
  1. 365
Platform
  1. MacOS
you can do a few things we could change the X to a different value
BUT keeping the X
then we would add 2 rules to conditional formatting
one would check for an X and Bi Weekly - say format RED
and 2nd rule for an X and Weekly - say format Green

Rule 1 = =AND( $E2="Bi Weekly", H2="X") for Bi Weekly and format Green
Rule 2 = =AND($E2="weekly", H2="X") for Weekly and format Green

Gantt-ETAF.xlsx
ABCDEFGHIJKLMNO
1Day of WeekFrequencyProject Start DateProject End DateWed 01/09/2021Thu 02/09/2021Fri 03/09/2021Sat 04/09/2021Sun 05/09/2021Mon 06/09/2021Tue 07/09/2021Wed 08/09/2021
2Status MeetingMonbi weekly1-Sep-211-Oct-21     X  
3Tuebi weekly1-Oct-2114-Oct-21        
4Wedweekly1-Sep-2131/9/21X      X
5Thubi weekly1-Sep-2131/9/21 X      
6Fribi weekly1-Sep-2131/9/21  X     
7Monweekly1-Sep-2131/9/21     X  
8Tuebi weekly1-Sep-2131/9/21      X 
9Wedbi weekly1-Sep-2131/9/21X       
10 
gantt (Mix)
Cell Formulas
RangeFormula
I1:O1I1=H1+1
H2:O9I2=IF(AND(WEEKDAY(I$1,2)=SWITCH($D2,"Mon",1,"TUE",2,"Wed",3,"Thu",4,"fri",5,""),I$1>=$F2,I$1<=$G2,IF(LEFT($E2,2)="Bi",COUNTIF(B2:H2,"X")=0,COUNTIF(H2,"X")=0)),"X","")
H10H10=IF(AND(WEEKDAY(H$1,2)=SWITCH($D10,"Mon",1,"TUE",2,"Wed",3,"Thu",4,"fri",5,""),H$1>=$F10,H$1<=$G10,COUNTIF(A10:G10,"X")=0),"X","")
Cells with Conditional Formatting
CellConditionCell FormatStop If True
H2:AR11Expression=AND($E2="weekly", H2="X")textNO
H2:AR11Expression=AND( $E2="Bi Weekly", H2="X")textNO



 

MarisaRaucci

New Member
Joined
Sep 21, 2021
Messages
7
Office Version
  1. 365
Platform
  1. Windows
Thank you I will give that a go on Mon and let you know how I go, have a great weekend.

Cheers
Marisa
 

Forum statistics

Threads
1,148,159
Messages
5,745,118
Members
423,925
Latest member
globaltlg

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