Hi everyone. I am trying to run multiple IF statements for a date selector and I am having some troubles. I feel like I am close but I believe my issue is how I am nesting everything.
I currently have an IF AND statement giving the logic of (THE LOGIC BELOW IS SET FOR CELL F7):
IF the Start Date (B7) is less than or equal to the date represented by cell (F5) AND the End Date (E7) is greater than or equal to the date represented by cell (F5) place a value of 1, if FALSE place a value of 0.
I have that working just fine. Basically, using conditional formatting, the Start date and End date will be entered and each date block automatically fills in.
A user can type in a Start Date and the amount of Hours needed to complete a project and the End Date will automatically updated. The End Date uses the WORKDAY function so Saturdays and Sundays are not counted
So here is my problem, I still want to show Saturdays and Sundays in the week day name row (F6:CQ6) but what I would like to happen is that when column 7 is equal to Sat or Sun, display a 2. I need that IF statement to be the highest priority because I would like to visually show weekends. Then, using more conditional formatting, each Saturday and Sunday will be a great block.
These all need to be dynamically generated because I need to be able to have a random Timeline start date for each project. (The Timeline Start Date B1 controls the first date in cell F5)
So here is what I have (representing the date for cell F7) so far and yes, it isnt working.
=IF(OR(F$7="sat",F$7="sun",2,IF(AND($B7<=F$5,$E7>=F$5),1,0)))
I am sure I am missing something obvious or I am nesting incorrectly. Any help is great.
Lastly, if the only way this can work is using VBA, can you please provide some detail? I am very, very new at VBA and I still am teaching myself how to use it.
Thank you everyone for your help in advanced. Attached is an example of what I am trying to do.
Bookmarks