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
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
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
 
Upvote 0
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
ABCDEFGHIJKLMNOPQRSTUVWXYZ
1Day of WeekFrequencyProject Start DateProject End DateFri 01/10/2021Sat 02/10/2021Sun 03/10/2021Mon 04/10/2021Tue 05/10/2021Wed 06/10/2021Thu 07/10/2021Fri 08/10/2021Sat 09/10/2021Sun 10/10/2021Mon 11/10/2021Tue 12/10/2021Wed 13/10/2021Thu 14/10/2021Fri 15/10/2021Sat 16/10/2021Sun 17/10/2021Mon 18/10/2021Tue 19/10/2021
2Status MeetingTuebi weekly1-Oct-211-Nov-21    X             X
Sheet2
Cell Formulas
RangeFormula
H2:Z2H2=IF(AND(WEEKDAY(H$1,2)=2,H$1>=$F2,H$1<=$G2,COUNTIF(A2:G2,"X")=0),"X","")
 
Upvote 0
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
ABCDEFGHIJKLMNOPQRSTUVWXYZ
1Day of WeekFrequencyProject Start DateProject End DateFri 01/10/2021Sat 02/10/2021Sun 03/10/2021Mon 04/10/2021Tue 05/10/2021Wed 06/10/2021Thu 07/10/2021Fri 08/10/2021Sat 09/10/2021Sun 10/10/2021Mon 11/10/2021Tue 12/10/2021Wed 13/10/2021Thu 14/10/2021Fri 15/10/2021Sat 16/10/2021Sun 17/10/2021Mon 18/10/2021Tue 19/10/2021
2Status MeetingTuebi weekly1-Oct-211-Nov-21    X             X
Sheet2
Cell Formulas
RangeFormula
H2:Z2H2=IF(AND(WEEKDAY(H$1,2)=2,H$1>=$F2,H$1<=$G2,COUNTIF(A2:G2,"X")=0),"X","")
 
Upvote 0
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.


1632292018608.png
 
Upvote 0
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


gantt sheet - etaf.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMANAOAPAQARASATAUAVAWAXAY
1LookupDay 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/2021Thu 09/09/2021Fri 10/09/2021Sat 11/09/2021Sun 12/09/2021Mon 13/09/2021Tue 14/09/2021Wed 15/09/2021Thu 16/09/2021Fri 17/09/2021Sat 18/09/2021Sun 19/09/2021Mon 20/09/2021Tue 21/09/2021Wed 22/09/2021Thu 23/09/2021Fri 24/09/2021Sat 25/09/2021Sun 26/09/2021Mon 27/09/2021Tue 28/09/2021Wed 29/09/2021Thu 30/09/2021Fri 01/10/2021Sat 02/10/2021Sun 03/10/2021Mon 04/10/2021Tue 05/10/2021Wed 06/10/2021Thu 07/10/2021Fri 08/10/2021Sat 09/10/2021Sun 10/10/2021Mon 11/10/2021Tue 12/10/2021Wed 13/10/2021Thu 14/10/2021
2MON1Status MeetingMonbi weekly1-Sep-211-Oct-21     X             X                        
3TUE2Tue1-Oct-2114-Oct-21                                  X         
4WED3Wed1-Sep-2131/9/21X             X             X             X 
5THU4Thu1-Sep-2131/9/21 X             X             X             X
6FRI5Fri1-Sep-2131/9/21  X             X             X             
7Mon1-Sep-2131/9/21     X             X             X          
8Tue1-Sep-2131/9/21      X             X             X         
9Wed1-Sep-2131/9/21X             X             X             X 
gantt
Cell Formulas
RangeFormula
I1:AY1I1=H1+1
H2:AY9H2=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","")
 
Upvote 0
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

Cell Formulas
RangeFormula
I1:AR1I1=H1+1
H2:AR8H2=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","")
 
Upvote 0
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

Cell Formulas
RangeFormula
I1:AR1I1=H1+1
H2:AR8H2=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
 
Upvote 0
=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
ABCDEFGHIJKLMNOPQ
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/2021Thu 09/09/2021Fri 10/09/2021
2Status MeetingMonweekly1-Sep-211-Oct-21     X    
3Tueweekly1-Oct-2114-Oct-21          
4Wedweekly1-Sep-2131/9/21X      X  
5Thuweekly1-Sep-2131/9/21 X      X 
6Friweekly1-Sep-2131/9/21  X      X
7Monweekly1-Sep-2131/9/21     X    
8Tueweekly1-Sep-2131/9/21      X   
9Wedweekly1-Sep-2131/9/21X      X  
gantt (week)
Cell Formulas
RangeFormula
I1:Q1I1=H1+1
H2:Q9H2=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

Cell Formulas
RangeFormula
I1:AJ1I1=H1+1
H2:AJ9H2=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","")


 
Upvote 0
Solution
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
 
Upvote 0

Forum statistics

Threads
1,224,823
Messages
6,181,183
Members
453,020
Latest member
Mohamed Magdi Tawfiq Emam

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
Back
Top