Gant Charts, Conditional Formatting and Resource Calculation

W

wesselsV

New Member
Joined
Apr 18, 2018
Messages
12
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:

[TABLE="width: 1200"]
<tbody>[TR]
[TD][/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[TD]L[/TD]
[TD]M[/TD]
[TD]N[/TD]
[TD]O[/TD]
[/TR]
[TR]
[TD]48[/TD]
[TD]Resource[/TD]
[TD]Quantity[/TD]
[TD]Start[/TD]
[TD]Finish[/TD]
[TD]Duration[/TD]
[TD]07-Jan-18[/TD]
[TD]14-Jan-18[/TD]
[TD]21-Jan-18[/TD]
[TD]28-Jan-18[/TD]
[TD]04-Feb-18[/TD]
[TD]11-Feb-18[/TD]
[/TR]
[TR]
[TD]49[/TD]
[TD]Operative[/TD]
[TD]2[/TD]
[TD]Fri 05 Jan 18[/TD]
[TD]Mon 05 Feb 18[/TD]
[TD]6.40[/TD]
[TD]0.4[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]0.4[/TD]
[/TR]
[TR]
[TD]50[/TD]
[TD]Supervisor[/TD]
[TD]1[/TD]
[TD]Fri 05 Jan 18[/TD]
[TD]Mon 05 Feb 18[/TD]
[TD]6.40[/TD]
[TD]0.4[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]0.4[/TD]
[/TR]
[TR]
[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]


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
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
Sort by date Sort by votes
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
 
Upvote 0
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
ABCDEFG
112-Feb-1819-Feb-1826-Feb-185-Mar-1812-Mar-18
2Sun 18 Feb 18Sun 04 Mar 180.1411
3Mon 19 Feb 18Mon 05 Mar 18110.14
4Tue 20 Feb 18Tue 06 Mar 180.8610.29
5Wed 21 Feb 18Wed 07 Mar 180.7110.43
6Thu 22 Feb 18Thu 08 Mar 180.5710.57
7Fri 23 Feb 18Fri 09 Mar 180.4310.71
8Sat 24 Feb 18Sat 10 Mar 180.2910.86
9Sun 25 Feb 18Sun 11 Mar 180.1411
10Mon 26 Feb 18Mon 12 Mar 18110.14
Sheet1
Cell Formulas
RangeFormula
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, "")))
 
Upvote 0
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".
 
Upvote 0
I've corrected for the five-day work week with this.


Book1
ABCDEFG
12018-02-122018-02-192018-02-262018-03-052018-03-12
2Mon 2018-02-19Mon 2018-03-05 110.2
3Tue 2018-02-20Tue 2018-03-060.810.4
4Wed 2018-02-21Wed 2018-03-070.610.6
5Thu 2018-02-22Thu 2018-03-080.410.8
6Fri 2018-02-23Fri 2018-03-090.211
7Mon 2018-02-26Mon 2018-03-12110.2
Sheet1
Cell Formulas
RangeFormula
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, "")))
 
Upvote 0
thisoldman said:
I've corrected for the five-day work week with this.

ABCDEFG

<colgroup><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]2018-02-12[/TD]
[TD="align: right"]2018-02-19[/TD]
[TD="align: right"]2018-02-26[/TD]
[TD="align: right"]2018-03-05[/TD]
[TD="align: right"]2018-03-12[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]Mon 2018-02-19[/TD]
[TD="align: right"]Mon 2018-03-05[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0.2[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]Tue 2018-02-20[/TD]
[TD="align: right"]Tue 2018-03-06[/TD]

[TD="align: right"]0.8[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0.4[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]Wed 2018-02-21[/TD]
[TD="align: right"]Wed 2018-03-07[/TD]

[TD="align: right"]0.6[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0.6[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]Thu 2018-02-22[/TD]
[TD="align: right"]Thu 2018-03-08[/TD]

[TD="align: right"]0.4[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0.8[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]Fri 2018-02-23[/TD]
[TD="align: right"]Fri 2018-03-09[/TD]

[TD="align: right"]0.2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]Mon 2018-02-26[/TD]
[TD="align: right"]Mon 2018-03-12[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0.2[/TD]

</tbody>
Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C2[/TH]
[TD="align: left"]=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, "")))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...

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?
 
Upvote 0
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
ABCDEFGHI
12018-02-122018-02-192018-02-262018-03-052018-03-12Holidays
2Tue 2018-02-20Thu 2018-02-22 0.62018-02-26
3Mon 2018-02-19Mon 2018-03-05110.252018-03-09
4Wed 2018-02-21Mon 2018-03-050.610.252018-03-12
5Thu 2018-02-22Thu 2018-03-080.4112018-12-25
6Fri 2018-02-23Fri 2018-03-090.211
7Mon 2018-02-26Mon 2018-03-12110
Sheet1 (2)
Cell Formulas
RangeFormula
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
NameRefers ToCells
Holidays='Sheet1 (2)'!$I$2:$I$5
 
Upvote 0
You must log in or register to reply here.

Similar threads

Vintage79
  • Solved
Count consecutive days and gaps between days
Replies
2
Views
218
Vintage79
Vintage79
K
  • Question Question
Dynamic Address for Use in Function Always returns ERROR
Replies
3
Views
549
Eric W
Eric W
M
  • Solved
Excel conditional formatting in a Gantt Chart
Replies
7
Views
3K
mb8marmed
M
K
  • Solved
Excel Percentage formulas and Pivot table chart displays
3 4 5
Replies
48
Views
3K
kellym9236
K
willow1985
  • Question Question
Assistance with Spreadsheet that is constantly crashing/over complicated formulas
Replies
1
Views
532
willow1985
willow1985

Forum statistics

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

Share this page

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