Quote:
Originally Posted by p45cal
This formula will highlight cells containing a date within 45 days of end of today()'s quarter:
Code:
=AND($H6<=EOMONTH(TODAY(),MOD(3-MONTH(TODAY()),3)),$H6>EOMONTH(TODAY(),MOD(3-MONTH(TODAY()),3))-45)
and this one will highlight the same as above plus any date prior to the start of today()'s quarter:
Code:
=OR(AND($H6<=EOMONTH(TODAY(),MOD(3-MONTH(TODAY()),3)),$H6>EOMONTH(TODAY(),MOD(3-MONTH(TODAY()),3))-45),$H6<DATE(YEAR(TODAY()),FLOOR(MONTH(TODAY())-1,3)+1,1))
|
Thank you for your response p45cal. So I think this is close but not quite what I'm looking for.
If, for instance, I insert 12/3/19 in the cell with your second formula, it will conditionally highlight because we are within 45 days of the end of the quarter (which ends 12/31). However, it should not highlight because I have done the task this quarter. I know what I'm asking is a bit tedious.
So the cell should highlight in these cases but not others:
-If the cell is blank and it's within 45 days of the end of the current quarter (meaning the user has not done the task at all because no date is recorded in the cell).
-If the cell has a date that is earlier than the previous quarter and today is within 45 days of the end of the quarter (meaning that, for instance, the user did the task in Q4 of last year and it is closer than 45 days to close in Q1 of this year so s/he didn't do it yet again, so the date in the cell is old. Similarly, the cell should highlight if the user did the task in Q3 and it's almost close of Q4, so again the date in the cell is old and we are getting close to the next quarter without a new date having been entered yet).