|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Conditionally Highlighting Blank Cells by Quarter in Current Calendar Year
Hi all,
So I understand that the following will return 12/31 of the current year: =DATE(YEAR(TODAY()), 12, 31) Can that be tweaked to highlight cells that remain blank after a quarter has passed (e.g., after March 31st of the current year?). The reason I'm doing current year is because this is a rolling spreadsheet that should automatically update each year. I was thinking: =AND($K5>=(YEAR(TODAY()),3,31),$K5="") But I get an error message that seems to mean that my formula syntax is wrong (I'm sure it is!). Any ideas? Thank you so much I love you guys! |
#2
|
|||
|
|||
I think this is working but will confirm in a bit after I have time to play with it more:
=AND($K5="",MONTH(E5)>3,YEAR(E5)=YEAR(TODAY())) where K5 is a cell that displays the last day of current year, and E5 is the active cell. |
#3
|
|||
|
|||
So in case it helps anyone in the future, my final version was as follows (the letters have changed because I'm working with different cells now. In these examples, M6 is the active cell):
=AND($M6="",TODAY()>=DATE(YEAR(TODAY()),3,31)-45) That conditionally highlights cell M6 if today's date is within 55 days of March 31st of the current calendar year. I added a couple more rules to highlight different colors when the cell is still blank within 30 days and 15 days of quarter close. To do the next quarter, you just change 3,31 to 6,30 for Q2, and so forth. If you don't want the condition about the cell being blank, I believe you could just use: =TODAY()>=DATE(YEAR(TODAY()),3,31)-45 |
Tags |
blank, conditional, current year |
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
VBA to move selected emails or conversation to current year pst folder | webharvest | Outlook | 1 | 08-15-2015 01:55 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 |
How to conditionally format cells in Col. A if it matches adjacent cell in Col. B? | alshcover | Excel | 2 | 06-03-2014 12:50 PM |
How to call current PC date and/or current PC year | KIM SOLIS | Excel | 2 | 11-04-2011 06:09 PM |
Auto insert current month's name and current year | Styler001 | Word | 4 | 01-25-2010 06:40 PM |