|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Conditional Highlighting When Approaching End of Quarter if Date in Cell is From Previous Quarter
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))) |
#2
|
||||
|
||||
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) 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)) |
#3
|
|||
|
|||
Quote:
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). |
#4
|
||||
|
||||
try:
Code:
=AND(TODAY()>EOMONTH(TODAY(),MOD(3-MONTH(TODAY()),3))-45,OR(LEN(TRIM($H6))=0,$H6<DATE(YEAR(TODAY()),FLOOR(MONTH(TODAY())-1,3)+1,1))) |
#5
|
|||
|
|||
Awesome, I think that did it! So if I wanted to make reminders that were every 6 months rather than every 3 months, would I change all the instances of 3 to 6?
|
#6
|
||||
|
||||
A cursory bit of experimentation suggests yes; why don't you try it?
|
#7
|
|||
|
|||
Fair point! It does seem to work. Thank you again for your assistance!
|
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Conditionally Highlighting Blank Cells by Quarter in Current Calendar Year | PrincessApril | Excel | 2 | 11-29-2019 03:31 AM |
Microsoft word Page has 4 quarter squares on each paper | AZn | Word | 3 | 05-18-2017 07:50 AM |
How to calculate a rolling year-to-date percentage by quarter as the year progresses | sleake | Excel Programming | 2 | 04-23-2015 11:51 AM |
Group Date by Quarter in PIVOT TABLE | shilabrow | Excel | 6 | 04-24-2014 10:44 AM |
Conditional Mail Merge per Student and Quarter | marysilvaramos | Mail Merge | 1 | 01-29-2013 07:17 PM |