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]

are the dates ALL bi weekly only ?
In your example the project starts on monday and you have a meeting the next day
In your example , If the project started on the tuesday , would the meeting be the following tuesday OR bi-weekly so 2 weeks , or the same day as the start date and then bi weekly
same for any start date , do you allow 2 clear weeks - so if started on a wednesday/thursday/friday , would the meeting be 2 weeks so only 11/12/13 days , or the first tuesday
Some examples of different start dates and when the meeting occurs may help

If the end of project is a tuesday - and a meeting due , would that be an X ?
if the project finished say on a Monday and last meeting was 2 weeks ago - I assume no meetings because project ends

this may need VBA , and so not something i answer,

but i thought a clearer definition of the rules may help, others
Also
I'm thinking of following the Gantt type examples i have setup before - with Start and end coloured - and milestones are set, and again coloured
But need to be sure of all the rules / permutations

so a few more examples would help

also a sample spreadsheet , with examples - loaded here , using XL2BB would be great
Otherwise i will look at shares like dropbox, onedrive, googlesheets

 

[etaf]

while I await your reply to the rules / conditions
can easily do every week - using a simple IF with weekday - just working on how to include the bi-weekly section , a countif() looking back 7 days wont work , as we need 7 columns before the 1st start date , unless you can just have the spreadhseet start with status meeting in column C


=IF(AND(WEEKDAY(F$1,2)=2,F$1>=$D3,F$1<=$E3),"X","")
then if the project info can always have 7 columns

we just add a countIF
=IF(AND(WEEKDAY(H$1,2)=2,H$1>=$F2,H$1<=$G2,COUNTIF(A2:G2,"X")=0),"X","")

I'm sure theres a better way then must have 7 columns before the date starts on the Gantt - other may reply with some offset, or other way to see...
anyway my suggestion

etaf.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z
1				Day of Week	Frequency	Project Start Date	Project End Date	Fri 01/10/2021	Sat 02/10/2021	Sun 03/10/2021	Mon 04/10/2021	Tue 05/10/2021	Wed 06/10/2021	Thu 07/10/2021	Fri 08/10/2021	Sat 09/10/2021	Sun 10/10/2021	Mon 11/10/2021	Tue 12/10/2021	Wed 13/10/2021	Thu 14/10/2021	Fri 15/10/2021	Sat 16/10/2021	Sun 17/10/2021	Mon 18/10/2021	Tue 19/10/2021
2			Status Meeting	Tue	bi weekly	1-Oct-21	1-Nov-21					X														X
Sheet2

Cell Formulas
Range	Formula
H2:Z2	H2	=IF(AND(WEEKDAY(H$1,2)=2,H$1>=$F2,H$1<=$G2,COUNTIF(A2:G2,"X")=0),"X","")

 

[MarisaRaucci]

while I await your reply to the rules / conditions
can easily do every week - using a simple IF with weekday - just working on how to include the bi-weekly section , a countif() looking back 7 days wont work , as we need 7 columns before the 1st start date , unless you can just have the spreadhseet start with status meeting in column C


=IF(AND(WEEKDAY(F$1,2)=2,F$1>=$D3,F$1<=$E3),"X","")
then if the project info can always have 7 columns

we just add a countIF
=IF(AND(WEEKDAY(H$1,2)=2,H$1>=$F2,H$1<=$G2,COUNTIF(A2:G2,"X")=0),"X","")

I'm sure theres a better way then must have 7 columns before the date starts on the Gantt - other may reply with some offset, or other way to see...
anyway my suggestion

etaf.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z
1				Day of Week	Frequency	Project Start Date	Project End Date	Fri 01/10/2021	Sat 02/10/2021	Sun 03/10/2021	Mon 04/10/2021	Tue 05/10/2021	Wed 06/10/2021	Thu 07/10/2021	Fri 08/10/2021	Sat 09/10/2021	Sun 10/10/2021	Mon 11/10/2021	Tue 12/10/2021	Wed 13/10/2021	Thu 14/10/2021	Fri 15/10/2021	Sat 16/10/2021	Sun 17/10/2021	Mon 18/10/2021	Tue 19/10/2021
2			Status Meeting	Tue	bi weekly	1-Oct-21	1-Nov-21					X														X
Sheet2

Cell Formulas
Range	Formula
H2:Z2	H2	=IF(AND(WEEKDAY(H$1,2)=2,H$1>=$F2,H$1<=$G2,COUNTIF(A2:G2,"X")=0),"X","")

 

[MarisaRaucci]

Hi,
Thank you for this, to answer your question, the bi weekly would always fall on the date that was identified for each client meeting, and only work days, therefore not including weekends. So if the meeting started on a Tue, the next meeting would be fortnightly on a Tue. If the project finished on a Mon, then yes the last meeting would have been two weeks ago. Ideally i would like to be able to list all the clients, ie Projects, and then enter the day of the week the meeting is scheduled, and then capture the dates via the gantt look, so similarly like a calendar, but using the gannt date style.

I've tried to load a spreadsheet but i cannot save it per the file type. Hopefully you can see the below example. So ideally i am looking to be able to see when all the recurring meetings occure, to then be able to plan all the pre meetings, and review sessions that would occur prior to the bi weekly meeting. Hopefully this captures all your questions.

 

[etaf]

can we add the 2 columns to the start of the text , so we have 7 columns before any dates - that allows the count() part to work
I cannot get a simple lookup array to work to pick up the day of the week, Mon/Tue/Wed etc and convert to a number
so maybe i need to rethink that , meanwhile i added a vlookup to convert days of week into a number

as i say not that happy with this so far - BUT it works
XL2BB below and a dropbox link

formula is =IF(AND(WEEKDAY(H$1,2)=VLOOKUP($D2,$A$2:$B$6,2,FALSE),H$1>=$F2,H$1<=$G2,COUNTIF(A2:G2,"X")=0),"X","")

This is the vlookup VLOOKUP($D2,$A$2:$B$6,2,FALSE), which can be on another sheet, I was hoping to use
lookup ( $D2, {"mon","Tue","wed","thu","fri";1,2,3,4,5}) But because of the 2 T Tue & Thu - it brings back the wrong value - I'm sure i have done this before , somewhere , maybe using switch/match/choose
any way

Dropbox

www.dropbox.com

gantt sheet - etaf.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT	AU	AV	AW	AX	AY
1	Lookup			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	Thu 09/09/2021	Fri 10/09/2021	Sat 11/09/2021	Sun 12/09/2021	Mon 13/09/2021	Tue 14/09/2021	Wed 15/09/2021	Thu 16/09/2021	Fri 17/09/2021	Sat 18/09/2021	Sun 19/09/2021	Mon 20/09/2021	Tue 21/09/2021	Wed 22/09/2021	Thu 23/09/2021	Fri 24/09/2021	Sat 25/09/2021	Sun 26/09/2021	Mon 27/09/2021	Tue 28/09/2021	Wed 29/09/2021	Thu 30/09/2021	Fri 01/10/2021	Sat 02/10/2021	Sun 03/10/2021	Mon 04/10/2021	Tue 05/10/2021	Wed 06/10/2021	Thu 07/10/2021	Fri 08/10/2021	Sat 09/10/2021	Sun 10/10/2021	Mon 11/10/2021	Tue 12/10/2021	Wed 13/10/2021	Thu 14/10/2021
2	MON	1	Status Meeting	Mon	bi weekly	1-Sep-21	1-Oct-21						X														X
3	TUE	2		Tue		1-Oct-21	14-Oct-21																																			X
4	WED	3		Wed		1-Sep-21	31/9/21	X														X														X														X
5	THU	4		Thu		1-Sep-21	31/9/21		X														X														X														X
6	FRI	5		Fri		1-Sep-21	31/9/21			X														X														X
7				Mon		1-Sep-21	31/9/21						X														X														X
8				Tue		1-Sep-21	31/9/21							X														X														X
9				Wed		1-Sep-21	31/9/21	X														X														X														X
gantt

Cell Formulas
Range	Formula
I1:AY1	I1	=H1+1
H2:AY9	H2	=IF(AND(WEEKDAY(H$1,2)=VLOOKUP($D2,$A$2:$B$6,2,FALSE),H$1>=$F2,H$1<=$G2,COUNTIF(A2:G2,"X")=0),"X","")

 

[etaf]

i dont know why i could not get switch() to work before, used on a different thread and it worked , so went back to my answer here and tried
formula is =IF(AND(WEEKDAY(H$1,2)=VLOOKUP($D2,$A$2:$B$6,2,FALSE),H$1>=$F2,H$1<=$G2,COUNTIF(A2:G2,"X")=0),"X","")
replaced by
=IF(AND(WEEKDAY(H$1,2)=SWITCH($D2,"Mon",1,"TUE",2,"Wed",3,"Thu",4,"fri",5,""),H$1>=$F2,H$1<=$G2,COUNTIF(A2:G2,"X")=0),"X","")
nolonger need the table and a lookup

Gantt-ETAF.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR
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	Thu 09/09/2021	Fri 10/09/2021	Sat 11/09/2021	Sun 12/09/2021	Mon 13/09/2021	Tue 14/09/2021	Wed 15/09/2021	Thu 16/09/2021	Fri 17/09/2021	Sat 18/09/2021	Sun 19/09/2021	Mon 20/09/2021	Tue 21/09/2021	Wed 22/09/2021	Thu 23/09/2021	Fri 24/09/2021	Sat 25/09/2021	Sun 26/09/2021	Mon 27/09/2021	Tue 28/09/2021	Wed 29/09/2021	Thu 30/09/2021	Fri 01/10/2021	Sat 02/10/2021	Sun 03/10/2021	Mon 04/10/2021	Tue 05/10/2021	Wed 06/10/2021	Thu 07/10/2021
2			Status Meeting	Mon	bi weekly	1-Sep-21	1-Oct-21						X														X
3				Tue		1-Oct-21	14-Oct-21																																			X
4				Wed		1-Sep-21	31/9/21	X														X														X
5				Thu		1-Sep-21	31/9/21		X														X														X
6				Fri		1-Sep-21	31/9/21			X														X														X
7				Mon		1-Sep-21	31/9/21						X														X														X
8				Tue		1-Sep-21	31/9/21							X														X														X
gantt

Cell Formulas
Range	Formula
I1:AR1	I1	=H1+1
H2:AR8	H2	=IF(AND(WEEKDAY(H$1,2)=SWITCH($D2,"Mon",1,"TUE",2,"Wed",3,"Thu",4,"fri",5,""),H$1>=$F2,H$1<=$G2,COUNTIF(A2:G2,"X")=0),"X","")

 

[MarisaRaucci]

i dont know why i could not get switch() to work before, used on a different thread and it worked , so went back to my answer here and tried
formula is =IF(AND(WEEKDAY(H$1,2)=VLOOKUP($D2,$A$2:$B$6,2,FALSE),H$1>=$F2,H$1<=$G2,COUNTIF(A2:G2,"X")=0),"X","")
replaced by
=IF(AND(WEEKDAY(H$1,2)=SWITCH($D2,"Mon",1,"TUE",2,"Wed",3,"Thu",4,"fri",5,""),H$1>=$F2,H$1<=$G2,COUNTIF(A2:G2,"X")=0),"X","")
nolonger need the table and a lookup

Gantt-ETAF.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR
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	Thu 09/09/2021	Fri 10/09/2021	Sat 11/09/2021	Sun 12/09/2021	Mon 13/09/2021	Tue 14/09/2021	Wed 15/09/2021	Thu 16/09/2021	Fri 17/09/2021	Sat 18/09/2021	Sun 19/09/2021	Mon 20/09/2021	Tue 21/09/2021	Wed 22/09/2021	Thu 23/09/2021	Fri 24/09/2021	Sat 25/09/2021	Sun 26/09/2021	Mon 27/09/2021	Tue 28/09/2021	Wed 29/09/2021	Thu 30/09/2021	Fri 01/10/2021	Sat 02/10/2021	Sun 03/10/2021	Mon 04/10/2021	Tue 05/10/2021	Wed 06/10/2021	Thu 07/10/2021
2			Status Meeting	Mon	bi weekly	1-Sep-21	1-Oct-21						X														X
3				Tue		1-Oct-21	14-Oct-21																																			X
4				Wed		1-Sep-21	31/9/21	X														X														X
5				Thu		1-Sep-21	31/9/21		X														X														X
6				Fri		1-Sep-21	31/9/21			X														X														X
7				Mon		1-Sep-21	31/9/21						X														X														X
8				Tue		1-Sep-21	31/9/21							X														X														X
gantt

Cell Formulas
Range	Formula
I1:AR1	I1	=H1+1
H2:AR8	H2	=IF(AND(WEEKDAY(H$1,2)=SWITCH($D2,"Mon",1,"TUE",2,"Wed",3,"Thu",4,"fri",5,""),H$1>=$F2,H$1<=$G2,COUNTIF(A2:G2,"X")=0),"X","")

Hi,
Apologies for my delay, I am in Australia and have just come off a public holiday and leave! Thanks so much, this is exactly what I was after. Can I ask you, if I wanted to change biweekly to weekly, what would I need to change in the formula to reflect this?

Cheers
Marisa

 

[etaf]

=IF(AND(WEEKDAY(H$1,2)=SWITCH($D2,"Mon",1,"TUE",2,"Wed",3,"Thu",4,"fri",5,""),H$1>=$F2,H$1<=$G2,COUNTIF(A2:G2,"X")=0),"X","")
to change to weekly - this section
COUNTIF(A2:G2,"X")=0
is looking back 7 days to see if there is an X
if its weekly no need to lookback, and check if X already exists
so
=IF(AND(WEEKDAY(H$1,2)=SWITCH($D2,"Mon",1,"TUE",2,"Wed",3,"Thu",4,"fri",5,""),H$1>=$F2,H$1<=$G2),"X","")

BUT if you want to have a mixture of BI & weekly - then we would need to add another IF ()
and the text in column E would be important
would it always be
Bi W - so the first 2 characters will always be Bi for Biweekly
and then for weekly - 1st 2 characters We
we can then use that
and they will Always be filled in ?
=IF(AND(WEEKDAY(H$1,2)=SWITCH($D2,"Mon",1,"TUE",2,"Wed",3,"Thu",4,"fri",5,""),H$1>=$F2,H$1<=$G2,IF(LEFT($E2,2)="Bi",COUNTIF(A2:G2,"X")=0,COUNTIF(G2,"X")=0)),"X","")

Weekly ONLY

Gantt-ETAF.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
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	Thu 09/09/2021	Fri 10/09/2021
2			Status Meeting	Mon	weekly	1-Sep-21	1-Oct-21						X
3				Tue	weekly	1-Oct-21	14-Oct-21
4				Wed	weekly	1-Sep-21	31/9/21	X							X
5				Thu	weekly	1-Sep-21	31/9/21		X							X
6				Fri	weekly	1-Sep-21	31/9/21			X							X
7				Mon	weekly	1-Sep-21	31/9/21						X
8				Tue	weekly	1-Sep-21	31/9/21							X
9				Wed	weekly	1-Sep-21	31/9/21	X							X
gantt (week)

Cell Formulas
Range	Formula
I1:Q1	I1	=H1+1
H2:Q9	H2	=IF(AND(WEEKDAY(H$1,2)=SWITCH($D2,"Mon",1,"TUE",2,"Wed",3,"Thu",4,"fri",5,""),H$1>=$F2,H$1<=$G2),"X","")

MIX of Bi and Weekly

Gantt-ETAF.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ
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	Thu 09/09/2021	Fri 10/09/2021	Sat 11/09/2021	Sun 12/09/2021	Mon 13/09/2021	Tue 14/09/2021	Wed 15/09/2021	Thu 16/09/2021	Fri 17/09/2021	Sat 18/09/2021	Sun 19/09/2021	Mon 20/09/2021	Tue 21/09/2021	Wed 22/09/2021	Thu 23/09/2021	Fri 24/09/2021	Sat 25/09/2021	Sun 26/09/2021	Mon 27/09/2021	Tue 28/09/2021	Wed 29/09/2021
2			Status Meeting	Mon	bi weekly	1-Sep-21	1-Oct-21						X														X
3				Tue	bi weekly	1-Oct-21	14-Oct-21
4				Wed	weekly	1-Sep-21	31/9/21	X							X							X							X							X
5				Thu	bi weekly	1-Sep-21	31/9/21		X														X
6				Fri	bi weekly	1-Sep-21	31/9/21			X														X
7				Mon	weekly	1-Sep-21	31/9/21						X							X							X							X
8				Tue	bi weekly	1-Sep-21	31/9/21							X														X
9				Wed	bi weekly	1-Sep-21	31/9/21	X														X														X
gantt (Mix)

Cell Formulas
Range	Formula
I1:AJ1	I1	=H1+1
H2:AJ9	H2	=IF(AND(WEEKDAY(H$1,2)=SWITCH($D2,"Mon",1,"TUE",2,"Wed",3,"Thu",4,"fri",5,""),H$1>=$F2,H$1<=$G2,IF(LEFT($E2,2)="Bi",COUNTIF(A2:G2,"X")=0,COUNTIF(G2,"X")=0)),"X","")

Dropbox

www.dropbox.com

 

[MarisaRaucci]

Thanks so much, you have saved me so much time and effort! This is working perfectly and just wanted to say thank you, you are very clever!

Cheers
Marisa