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:

Mon

Tues

Wed

Thur

Fri

Mon

Tues

Wed

Thur

Fri

Mon

Tues

Wed

Thur

Fri

Mon

Tues

Wed

Thur

Fri

Mon

Tues

Wed

Thur

Fri

Task ID

Name of EW

Task Duration

Start date

End Date

03/09/2018

04/09/2018

05/09/2018

06/09/2018

07/09/2018

10/09/2018

11/09/2018

12/09/2018

13/09/2018

14/09/2018

17/09/2018

18/09/2018

19/09/2018

20/09/2018

21/09/2018

24/09/2018

25/09/2018

26/09/2018

27/09/2018

28/09/2018

01/10/2018

02/10/2018

03/10/2018

04/10/2018

05/10/2018

Stuart

315

10/09/2018

22/07/2019

###

X

X

X

315

10/09/2018

22/07/2019

X

X

X

X

61

01/10/2018

01/12/2018

X

61

01/10/2018

01/12/2018

X

70

29/04/2019

08/07/2019

70

07/01/2019

18/03/2019

70

29/04/2019

08/07/2019

70

07/01/2019

18/03/2019

315

07/09/2018

19/07/2019

X

X

X

X

X

318

07/09/2018

22/07/2019

X

X

X

X

0

<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>
</tbody>

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