Bi weekly formula...

[Iain McBride]

Hi - trying to formulate a Gantt chart to allow me to show where some of our employees will be on a daily basis.

So far I have the formula:
=IF(AND(G$8="Tues",G$9>=$D17,G$9<=$E17),"X","")

This allows me to highlight where people are (in this example) every Tuesday - is there a way to highlight the areas they work on a bi-weekly basis?

Thanks in advance

Iain

 

[James006]

Hi,

Do not know what bi-weekly means to you ... and since you seem not to use actual dates ...

May be something along this line could help :

=IF(AND(OR(G$8="Tues",G$8="Thur"),G$9>=$D17,G$9<=$E17),"X","")

 

[Iain McBride]

Hi James,
The formula highlights where the employee is every Tuesday - however there are instances whereby they are working every OTHER Tuesday - and trying to find a formula to work for that element (if possible)

 

[James006]

Hi,

As I said earlier ... you seem not to use actual dates ... is that right ...?

Is there a number of fixed columns between your Tuesdays ...?

 

[Iain McBride]

James - I have amended the formula to:

=IF(AND(P$8="Thur",P$9>=$D10,P$9<=$E10),"X","")

and the spreadsheet looks like:

[TABLE="width: 1184"]
<colgroup><col><col><col><col><col><col><col span="2"><col><col><col><col span="3"><col><col><col span="3"><col><col><col span="3"><col><col><col span="3"><col></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]Mon[/TD]
[TD="align: right"]Tues[/TD]
[TD="align: right"]Wed[/TD]
[TD="align: right"]Thur[/TD]
[TD="align: right"]Fri[/TD]
[TD="align: right"]Mon[/TD]
[TD="align: right"]Tues[/TD]
[TD="align: right"]Wed[/TD]
[TD="align: right"]Thur[/TD]
[TD="align: right"]Fri[/TD]
[TD="align: right"]Mon[/TD]
[TD="align: right"]Tues[/TD]
[TD="align: right"]Wed[/TD]
[TD="align: right"]Thur[/TD]
[TD="align: right"]Fri[/TD]
[TD="align: right"]Mon[/TD]
[TD="align: right"]Tues[/TD]
[TD="align: right"]Wed[/TD]
[TD="align: right"]Thur[/TD]
[TD="align: right"]Fri[/TD]
[TD="align: right"]Mon[/TD]
[TD="align: right"]Tues[/TD]
[TD="align: right"]Wed[/TD]
[TD="align: right"]Thur[/TD]
[TD="align: right"]Fri[/TD]
[/TR]
[TR]
[TD]Task ID[/TD]
[TD]Name of EW[/TD]
[TD]Task Duration[/TD]
[TD]Start date[/TD]
[TD]End Date[/TD]
[TD="align: right"]03/09/2018[/TD]
[TD="align: right"]04/09/2018[/TD]
[TD="align: right"]05/09/2018[/TD]
[TD="align: right"]06/09/2018[/TD]
[TD="align: right"]07/09/2018[/TD]
[TD="align: right"]10/09/2018[/TD]
[TD="align: right"]11/09/2018[/TD]
[TD="align: right"]12/09/2018[/TD]
[TD="align: right"]13/09/2018[/TD]
[TD="align: right"]14/09/2018[/TD]
[TD="align: right"]17/09/2018[/TD]
[TD="align: right"]18/09/2018[/TD]
[TD="align: right"]19/09/2018[/TD]
[TD="align: right"]20/09/2018[/TD]
[TD="align: right"]21/09/2018[/TD]
[TD="align: right"]24/09/2018[/TD]
[TD="align: right"]25/09/2018[/TD]
[TD="align: right"]26/09/2018[/TD]
[TD="align: right"]27/09/2018[/TD]
[TD="align: right"]28/09/2018[/TD]
[TD="align: right"]01/10/2018[/TD]
[TD="align: right"]02/10/2018[/TD]
[TD="align: right"]03/10/2018[/TD]
[TD="align: right"]04/10/2018[/TD]
[TD="align: right"]05/10/2018[/TD]
[/TR]
[TR]
[TD]Stuart[/TD]
[TD] [/TD]
[TD]315[/TD]
[TD]10/09/2018[/TD]
[TD]22/07/2019[/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD="align: center"]###[/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD]X[/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD]X[/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD]X[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]315[/TD]
[TD]10/09/2018[/TD]
[TD]22/07/2019[/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]X[/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]X[/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]X[/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]X[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]61[/TD]
[TD]01/10/2018[/TD]
[TD]01/12/2018[/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD]X[/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]61[/TD]
[TD]01/10/2018[/TD]
[TD]01/12/2018[/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD]X[/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]70[/TD]
[TD]29/04/2019[/TD]
[TD]08/07/2019[/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]70[/TD]
[TD]07/01/2019[/TD]
[TD]18/03/2019[/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]70[/TD]
[TD]29/04/2019[/TD]
[TD]08/07/2019[/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]70[/TD]
[TD]07/01/2019[/TD]
[TD]18/03/2019[/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]315[/TD]
[TD]07/09/2018[/TD]
[TD]19/07/2019[/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]X[/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]X[/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]X[/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]X[/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]X[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]318[/TD]
[TD]07/09/2018[/TD]
[TD]22/07/2019[/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD]X[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD]X[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD]X[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD]X[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]0[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]

So the days are running across the column (with weekends there but hidden).

Not sure if that adds any more clarity or not?!

 

[James006]

Iain,

First the good news... you are actually using Dates and Not Text ....

In which cell are you using the formula shown ?

Is the first Monday in Column F ....?

 

[Iain McBride]

Sorry for the lack of info - struggling to get my head around this (can you tell?? lol)

Yes Monday is in Column F

I copied the formula from Cell P10

Iain

 

[James006]

Well ... little by little ... you will get there ...

Say we are dealing with Row 10 ... which I understand is the First row of your table ...

Is your objective to show an X ... for all the Thursdays which appear within the time frame defined by cells D10 (start date) and E10 (end date) ...

 

[Iain McBride]

The formula applied shows every Thursday - I am trying to show every OTHER Thursday

 

[James006]

By the way ... to be on the safe side ... can you confirm the following :

1. In cell F9 there is an input date 03-09-2018 and all following dates in row 9 have in fact the formula = F9 + 1 copied till the last column ...

2. In cell F8 ... you have the formula = F9 ... and the custom format applied : ddd