Gant Charts, Conditional Formatting and Resource Calculation

[wesselsV]

Hello,

I am trying to set up a formula in a workbook to firstly highlight a range of cells between two given dates. Secondly I am trying to populate these cells based on start and finish dates and the quantity of resource for a given item.

Duration

Column J =((H49-G49)/5)+0.2 (Where 0.2 is 1 Day and a 5 Day Week = 1)

Duration currently = 6.4 but surely this should be 4.4?

Conditional Formatting (Formula Used)

In Cell J49:

=IF(J$7>$H49,"",IF(K$7>$G49,1,""))

This Applies to =$J$49:$DZ$60

The Source Data is set out below:

	E	F	G	H	I	J	K	L	M	N	O
48	Resource	Quantity	Start	Finish	Duration	07-Jan-18	14-Jan-18	21-Jan-18	28-Jan-18	04-Feb-18	11-Feb-18
49	Operative	2	Fri 05 Jan 18	Mon 05 Feb 18	6.40	0.4	2	2	2	2	0.4
50	Supervisor	1	Fri 05 Jan 18	Mon 05 Feb 18	6.40	0.4	2	2	2	2	0.4

<tbody>
</tbody>

The cell range J49:O50 contains the outputs based on the working week and the quantity of resources.

I am struggling to apply a formula which can replicate this, can anyone provide me with some guidance?

Thanks

 

[wesselsV]

I've had another go at this and used a Network Days Formula to give me the duration. However, when I have an activity that runs across 3 weeks there should be 3 cells highlighted in a colour but so far there is only 2. See Row 12 on the attached sheet.

The conditional format in these cells is =AND($I8<=L$6,$J8>=L$6). I'm also trying to spread the value of 1.6 weeks across 3 weeks i.e. 0.4, 1 and 0.2 and I've tried =IF(AND(T$6>=$I12, U$6<=$J12),1,"") but to no avail.

https://1drv.ms/x/s!As-9OWf6QipWg0sKOuxpXANMUf8D

 

[thisoldman]

I could not work with your cell formatting; you will have to correct the range references in the formula. This should make your conditional formatting easier—simply color the cells not equal to "".

Book1
	A	B	C	D	E	F	G
1			12-Feb-18	19-Feb-18	26-Feb-18	5-Mar-18	12-Mar-18
2	Sun 18 Feb 18	Sun 04 Mar 18	0.14	1	1
3	Mon 19 Feb 18	Mon 05 Mar 18		1	1	0.14
4	Tue 20 Feb 18	Tue 06 Mar 18		0.86	1	0.29
5	Wed 21 Feb 18	Wed 07 Mar 18		0.71	1	0.43
6	Thu 22 Feb 18	Thu 08 Mar 18		0.57	1	0.57
7	Fri 23 Feb 18	Fri 09 Mar 18		0.43	1	0.71
8	Sat 24 Feb 18	Sat 10 Mar 18		0.29	1	0.86
9	Sun 25 Feb 18	Sun 11 Mar 18		0.14	1	1
10	Mon 26 Feb 18	Mon 12 Mar 18			1	1	0.14
Sheet1

Cell Formulas
Range	Formula
C2	=IF(AND(WEEKNUM(C$1,2)>WEEKNUM($A2,2), WEEKNUM(C$1,2)< WEEKNUM($B2,2)), 1, IF(WEEKNUM(C$1,2)=WEEKNUM($A2,2), 1-($A2-C$1)/7, IF(WEEKNUM(C$1,2)=WEEKNUM($B2,2), (1+$B2-C$1)/7, "")))

 

[wesselsV]

Hi,

Thanks for your response. I've changed the divisor to 5 based on a 5 day working week however I seem to be having an issue where both the start and end date both occur in the same week.

Shouldn't =IF(AND(WEEKNUM(C$1,2)>WEEKNUM($A2,2), WEEKNUM(C$1,2)< WEEKNUM($B2,2)), 1, IF(WEEKNUM(C$1,2)=WEEKNUM($A2,2), 1-($A2-C$1)/7,IF(WEEKNUM(C$1,2)=WEEKNUM($B2,2), (1+$B2-C$1)/7, ""))) read something like '=IF((AND(WEEKNUM(D$1,2)=WEEKNUM($A3,2),WEEKNUM(D$1,2)=(WEEKNUM($B3,2),((B3-A3)/5)) ?

So for example in column A2 and B2 where there is a start and finish date of say 08 Jan and 10 Jan 2018 0.6 days should appear however in the current format this is rounded up to "1".

 

[thisoldman]

I've corrected for the five-day work week with this.

Book1
	A	B	C	D	E	F	G
1			2018-02-12	2018-02-19	2018-02-26	2018-03-05	2018-03-12
2	Mon 2018-02-19	Mon 2018-03-05		1	1	0.2
3	Tue 2018-02-20	Tue 2018-03-06		0.8	1	0.4
4	Wed 2018-02-21	Wed 2018-03-07		0.6	1	0.6
5	Thu 2018-02-22	Thu 2018-03-08		0.4	1	0.8
6	Fri 2018-02-23	Fri 2018-03-09		0.2	1	1
7	Mon 2018-02-26	Mon 2018-03-12			1	1	0.2
Sheet1

Cell Formulas
Range	Formula
C2	=IF(AND(WEEKNUM(C$1,2)>WEEKNUM($A2,2), WEEKNUM(C$1,2)< WEEKNUM($B2,2)), 1, IF(WEEKNUM(C$1,2)=WEEKNUM($A2,2), (6- WEEKDAY($A2, 2))/5, IF(WEEKNUM(C$1,2)=WEEKNUM($B2,2), WEEKDAY($B2, 2)/5, "")))

 

[wesselsV]

I've corrected for the five-day work week with this.

	A	B	C	D	E	F	G
1			2018-02-12	2018-02-19	2018-02-26	2018-03-05	2018-03-12
2	Mon 2018-02-19	Mon 2018-03-05		1	1	0.2
3	Tue 2018-02-20	Tue 2018-03-06		0.8	1	0.4
4	Wed 2018-02-21	Wed 2018-03-07		0.6	1	0.6
5	Thu 2018-02-22	Thu 2018-03-08		0.4	1	0.8
6	Fri 2018-02-23	Fri 2018-03-09		0.2	1	1
7	Mon 2018-02-26	Mon 2018-03-12			1	1	0.2

<colgroup><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula C2 =IF(AND(WEEKNUM(C$1,2)>WEEKNUM($A2,2), WEEKNUM(C$1,2)< WEEKNUM($B2,2)), 1, IF(WEEKNUM(C$1,2)=WEEKNUM($A2,2), (6- WEEKDAY($A2, 2))/5, IF(WEEKNUM(C$1,2)=WEEKNUM($B2,2), WEEKDAY($B2, 2)/5, ""))) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Thanks for that. I still appear to have an issue with the start and finish date in such instance say where start and finish date of say 08 Jan and 10 Jan 2018 0.6 days should appear however in the current format this is rounded up to "1". Also, is there a nuance to this formula which will account for years? The WEEKNUM format only appears to work within the same calendar year?

 

[thisoldman]

Try this.

It will correct for holidays, if you have the list of holidays in a defined name. If you don't want the holidays correction, everywhere you see NETWORKDAYS(< Start >, < End >, Holidays) change the function arguments to NETWORKDAYS(< Start >, < End >).

With holidays, if the end date is a Monday, and that Monday is a holiday, the Gantt will display a zero.

Book1
	A	B	C	D	E	F	G	H	I
1			2018-02-12	2018-02-19	2018-02-26	2018-03-05	2018-03-12		Holidays
2	Tue 2018-02-20	Thu 2018-02-22		0.6					2018-02-26
3	Mon 2018-02-19	Mon 2018-03-05		1	1	0.25			2018-03-09
4	Wed 2018-02-21	Mon 2018-03-05		0.6	1	0.25			2018-03-12
5	Thu 2018-02-22	Thu 2018-03-08		0.4	1	1			2018-12-25
6	Fri 2018-02-23	Fri 2018-03-09		0.2	1	1
7	Mon 2018-02-26	Mon 2018-03-12			1	1	0
Sheet1 (2)

Cell Formulas
Range	Formula
C2	=IF(($A2>C$1+4)+($B2< C$1), "", IF(($A2< C$1)*($B2>C$1+4), 1, IF(($A2>=C$1)*($A2<=C$1+4)*($B2<=C$1+4), NETWORKDAYS($A2, $B2, Holidays)/NETWORKDAYS(C$1, C$1+4, Holidays), IF(($A2>=C$1)*($A2<=C$1+4)*($B2>C$1+4), NETWORKDAYS($A2, C$1+4, Holidays)/NETWORKDAYS(C$1, C$1+4, Holidays), IF(($A2< C$1)*($B2<=C$1+4), NETWORKDAYS(C$1, $B2, Holidays)/NETWORKDAYS(C$1, C$1+4, Holidays))))))

Named Ranges
Name	Refers To	Cells
Holidays	='Sheet1 (2)'!$I$2:$I$5