I've developed the following formula that will conditionally highlight a cell as the end of each quarter approaches (beginning 45 days prior to quarter end).
The same cell is being reused for each quarter (otherwise we will have too many columns in the worksheet). In Q1 (Quarter 1), the cell should conditionally highlight if the cell is blank and within 45 days of Q1 end (I think I have that part right). For Q2, the cell should highlight if within 45 days of end of Q2 and the cell is either blank or has a date from the prior quarter. Then for Q3 the reminder would be if blank or if a date from either previous quarter (Q1 or Q2). And finally for Q4 that would highlight if blank or if a date from anywhere in Q1-Q3).
Here is what I'm working with so far but getting an error (syntax I think--it's a dialogue box that says "there is a problem with this formula"):
Code:
=OR(AND($H6="",(TODAY()>=DATE(YEAR(TODAY()),3,31)-45))),(AND($H6=>=DATE(Year(TODAY()),1, 1),$H6<=DATE(YEAR(TODAY()),3,31)),TODAY()>=DATE(YEAR(TODAY()),6,30)-45),(AND($H6=>=DATE(Year(TODAY()),1, 1),$H6<=DATE(YEAR(TODAY()),6,30)),TODAY()>=DATE(YEAR(TODAY()),9,30)-45),(AND($H6=>=DATE(Year(TODAY()),1, 1),$H6<=DATE(YEAR(TODAY()),9,30)),TODAY()>=DATE(YEAR(TODAY()),12,31)-45)))
Am I on the right track, and if so, what might be causing the error? Thanks in advance for any assistance!