Gantt chart for time sheet formulae working only 1st row

[Cambosity]

Hi all and seasons greetings. :) I have taken a formula suggested from google to make a gantt chart for my employees roster time sheet,

IF($B5<$C5,AND(D$4>=$B5,D$4<=$C5),OR(D$4>=$B5,D$4<$C5))

Plan was in cell B5 put this into conditional formatting then drag to cover all cells. This works for the first row but doesn't when dragging down. The finish time works but the start doesn't... Have tried to fix myself but can't work out why.... 

Sample below. Have been working on some other stuff related so it has got some random experiment formulas round the place....

Any thoughts?

[#2]

@Cambosity On row 24, columns BA:DB, your SUM formula includes the row that contains the shift number for every other column (the ones with the start times). Change the values on row 24 to type General and you see the consequences. For instance, column BB shows a total of 2.5, being the shift number plus the number value of the time 12:00 (i.e. 0.5 of 1 day). Formatting the number 2.5 as h:mm will display 12:00 as Excel only displays the time portion of 2.5 days. Copy the formula from AZ24 across and it shall work.

[#3]

@Cambosity 

Another way how to fix what @Riny_van_Eekelen explains

- in Start / End columns instead of =AZ24 use =MOD(AZ24,1)

- Formula for conditional formatting rule I'd adjust as

=(D$4>=MEDIAN(D$4,$B5-1/10000,$C5+1/10000))*(D$4<=MEDIAN(D$4,$B5-1/10000,$C5+1/10000))*($C5>$B5)

to exclude not defined shifts and fix possible rounding errors in time calculations

[#4]

@Sergei Baklan 

Sorry for the slow reply I have been away for some time. Your formula works beautifully.... Thankyou ! and Happy New Year !

[#5]

@Riny_van_Eekelen 

Thankyou and sorry for my slow reply. I have been away. I decided to use the formula from Sergei and does well !! Happy New Year !