Using IF, AND together with Start/End Dates

[Ooney]

Hi,

I'm new and found the forum because I need some help to figure something out.

So I have task start and end dates and am trying to get the cells to automatically shade in the weeks in which the task is being done. I have set it up with conditional formatting so when there's a "Y" it will shade blue. How to get the "Y" is where I'm having trouble.

So far I've tried the following 2 formulas and it's not working:
=IF(AND($D82>=G$13,$E82<=G$12),"Y","N")
=IF((G$13>=$D83),IF((G$13<=$E83),"Y","N"),"N")

Hopefully this photo makes my problem more clear.

Excel Date Question.JPG

Any ideas would be very helpful!

Thank you

[protonLeah]

To attach a Workbook <<<<<<<<<<<
(please do not post pictures of worksheets)

• Click Advanced (next to quick post),
• Scroll down until you see "Manage Attachments",
• Click that then select "add files" (top right corner).
• Click "Select Files" find your file, click "open" click "upload"
• Once the upload is completed the file name will appear below the input boxes in this window.
• Click "Done" at bottom right to close the Attachment Manager.
• Click "Submit Reply"

[Ooney]

Thanks B. Johnson!
That would make things a lot easier to understand indeed! Thanks for the tip. Have attached it now

Test Workbook.xlsx

[protonLeah]

Seems to work:

Please Login or Register  to view this content.

[Ooney]

Forgot to mention that it works for some situations but not all - I've highlighted the boxes in red that should have been shaded blue but I can't figure out why it isn't.

Most of the times it happens because the start date doesn't match up exactly with the week date (at the top). I had put the end of the week dates in when I was playing with the formula as I wasn't sure if that was having an impact...

Seems to work:

Please Login or Register  to view this content.

[protonLeah]

G3 = 12/16/2013
B8 = 12/19/2014
C8 = 01/09/2014

Therefore:
=IF(AND(G$3>=$B8,G$3<=$C8),"Y","N") = False ="N"