Formula Need for Gantt Chart/Project Plan

[cjames83]

Hi. I'm having issues with a formula and I can't figure out how to fix it. I have the formula =IF(AND(R$2>=$C4,R$2<=$D4),1,0) in R4. What I would like to happen is to enter a start date and end date in Columns C & D and have the applicable columns to follow highlight using conditional formatting. The formula is partially working. The issue is that if the Start Date (Column C) isn't exactly what the date is in Row 2 then that box won't highlight. For instance, right now R4 is currently highlighted because the start date is 4/30. If I were to change that start date to 5/1 then the box would un-highlight.

My understanding of the formula is "if R2 (4/30) is after or the same day as C4 (Start Date 4/30) AND R2 (4/30) is before or the same day as D4 (End Date 5/1) then the field's value is 1 which then triggers my conditional formatting to highlight it. How do I revise this formula so that that R4 would stay highlighted if any date between 4/30 and 5/6 is in the start date field? Thanks in advance for the assistance!

 

[thisoldman]

Try this.

Dates in row 2 should contain the year for this to work. They can be formatted to "m/d" if that's what you wish to display.

Then use this formula in F2, copied to the right and downward to return TRUE or FALSE.
=AND(WEEKNUM(F$2,2)>=WEEKNUM($C3,2),WEEKNUM(F$2,2)<=WEEKNUM($D3,2))

 

[cjames83]

Thank you for your help! I apologize that it took me so long to respond. I got backlogged on emails and I'm just now getting around to trying it. It works like a charm except for one thing. The issue is that I have a couple of years listed on my spreadsheet. While it does highlight the correct date range, it highlights that range every year instead of just the year indicated in columns C & D.

I did check to make sure I had the dates in row 2 formatted with the year but only showing MM-DD as suggested. I also verified that row 2 and columns C & D are all formatted as "Dates". Do you have any idea why this could be happening?

Again, your help with this is most appreciated!

Try this.

Dates in row 2 should contain the year for this to work. They can be formatted to "m/d" if that's what you wish to display.

Then use this formula in F2, copied to the right and downward to return TRUE or FALSE.
=AND(WEEKNUM(F$2,2)>=WEEKNUM($C3,2),WEEKNUM(F$2,2)<=WEEKNUM($D3,2))

 

[thisoldman]

I assumed you needed only one year at a time. I think this should work.

=AND(YEAR(F$4=YEAR($C3),(WEEKNUM(F$4,2)>=WEEKNUM($C3,2),WEEKNUM(F$4,2)<=WEEKNUM($D3,2))

 

[cjames83]

Thank you again for your reply. I tried the formula but it is not working for some reason. I tried it as you wrote it and I also tweeked it a little to match the actual cells I need it to reference. The end result was

=AND(YEAR(F$2)=YEAR($C4)),(WEEKNUM(F$2,2)>=WEEKNUM($C4,2),WEEKNUM(F$2,2)<=WEEKNUM($D4,2))

The error I'm getting is that there is a problem with the formula. Any idea what it could be? I tried the few possible things I could think of but I've never worked with the YEAR or WEEKNUM functions so my problem solving skills as it relates to this are limited at best. One thing I did notice is that when I put the formula in the function bar, the last cell reference (D4) isn't highlighted at all but the other cell references are.

I assumed you needed only one year at a time. I think this should work.

=AND(YEAR(F$4=YEAR($C3),(WEEKNUM(F$4,2)>=WEEKNUM($C3,2),WEEKNUM(F$4,2)<=WEEKNUM($D3,2))

 

[thisoldman]

Can you post a mockup of what you have so that I can copy the data into Excel. It's difficult working with an image, an image that is at the limits of my senior vision.

 

[cjames83]

Sure! Here is a link to the file in my google drive. When you click it will open in your web browser and you'll also have the option to download it if you want so you can work in it directly. This is the only method I know of to share it. If there is another way I'm suppose to do it on this forum, please let me know.

https://drive.google.com/open?id=1XXUFMzoxU3XPoq4iqNFpIbVqR8dWywaB

Can you post a mockup of what you have so that I can copy the data into Excel. It's difficult working with an image, an image that is at the limits of my senior vision.

 

[thisoldman]

Here's one that appears to work:
=AND(F$2 + 6 >= $C4, $D4 + 7 > F$2 + 6)

 

[cjames83]

You are the excel MASTER! Thank you so much! I really appreciate your help with this!!

Here's one that appears to work:
=AND(F$2 + 6 >= $C4, $D4 + 7 > F$2 + 6)

 

[thisoldman]

You're welcome. I'm happy to have helped.