Need help about formula

[ybr_15]

Hi, I have shift calendar template from this site

I am a bit puzzled to explain so I will use the following image below:

---

Based the image, I have schedule like this:

Then if I see highlight in the calendar, I get wrong highlight (I marked with a red horizontal line). The range in red horizontal line should not be highlighted. The problem is with this formula:
<mindate;b5><mindate;b5><mindate;b5><mindate;b5><mindate;b5><mindate;b5>

Can anyone here help me to fix the formula so I get highlight to work properly like I want?

If you need file, here example file that I edited:
https://www.dropbox.com/s/ixq14dqoondegvu/shift-calendar-template-EDIT.xlsx?dl=0

Thanks..</mindate;b5></mindate;b5></mindate;b5></mindate;b5></mindate;b5></mindate;b5>

 

[wideboydixon]

I don't think the template is designed to cater for breaks between shifts so it assumes that the previous shift starts on the date before the next one. In your case, "Vacation" starts on 16-Jan so it assumes "Day Shift" ends on 15-Jan. It would need some work to cater for non-sequential shifts. Try this formula in B15 and copy across and down:

=IF(OR(B5<minDate,B5>maxDate),0,IFERROR(IF(B5<=VLOOKUP(B5,tblShifts,2),VLOOKUP(B5,tblShifts,3)*(MONTH(B5)=$C$12),0),0))

WBD

 

[ybr_15]

Hi, wideboydixon

Thanks alot. Your code work properly

Btw, Can you tell me a little about your formula work? thank you

 

[wideboydixon]

I just changed the formula to check the date is also before the end date of the shift:

B5<=VLOOKUP(B5,tblShifts,2)

WBD