Recurring tasks using Gantt Charts and Conditional Formatting

[MarisaRaucci]

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 October	Tue 2 Oct
	Day of Week	Frequency	Project Start Date	Project End Date
Status Meeting	Tue	bi weekly	1 October 2021	1 November 2021		X

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]

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

 

[MarisaRaucci]

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]

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
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1				Day of Week	Frequency	Project Start Date	Project End Date	Wed 01/09/2021	Thu 02/09/2021	Fri 03/09/2021	Sat 04/09/2021	Sun 05/09/2021	Mon 06/09/2021	Tue 07/09/2021	Wed 08/09/2021
2			Status Meeting	Mon	bi weekly	1-Sep-21	1-Oct-21						X
3				Tue	bi weekly	1-Oct-21	14-Oct-21
4				Wed	weekly	1-Sep-21	31/9/21	X							X
5				Thu	bi weekly	1-Sep-21	31/9/21		X
6				Fri	bi weekly	1-Sep-21	31/9/21			X
7				Mon	weekly	1-Sep-21	31/9/21						X
8				Tue	bi weekly	1-Sep-21	31/9/21							X
9				Wed	bi weekly	1-Sep-21	31/9/21	X
10
gantt (Mix)

Cell Formulas
Range	Formula
I1:O1	I1	=H1+1
H2:O9	I2	=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","")
H10	H10	=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
Cell	Condition		Cell Format	Stop If True
H2:AR11	Expression	=AND($E2="weekly", H2="X")	text	NO
H2:AR11	Expression	=AND( $E2="Bi Weekly", H2="X")	text	NO

Dropbox

www.dropbox.com

 

[MarisaRaucci]

Thank you I will give that a go on Mon and let you know how I go, have a great weekend.

Cheers
Marisa