Microsoft Office Forums Conditionally Highlighting Blank Cells by Quarter in Current Calendar Year

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 11-27-2019, 08:01 AM
PrincessApril PrincessApril is offline Conditionally Highlighting Blank Cells by Quarter in Current Calendar Year Windows 10 Conditionally Highlighting Blank Cells by Quarter in Current Calendar Year Office 2019
Novice
Conditionally Highlighting Blank Cells by Quarter in Current Calendar Year
 
Join Date: Nov 2019
Posts: 24
PrincessApril is on a distinguished road
Default 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!
Reply With Quote
  #2  
Old 11-27-2019, 10:32 AM
PrincessApril PrincessApril is offline Conditionally Highlighting Blank Cells by Quarter in Current Calendar Year Windows 10 Conditionally Highlighting Blank Cells by Quarter in Current Calendar Year Office 2019
Novice
Conditionally Highlighting Blank Cells by Quarter in Current Calendar Year
 
Join Date: Nov 2019
Posts: 24
PrincessApril is on a distinguished road
Default

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.
Reply With Quote
  #3  
Old 11-29-2019, 03:31 AM
PrincessApril PrincessApril is offline Conditionally Highlighting Blank Cells by Quarter in Current Calendar Year Windows 10 Conditionally Highlighting Blank Cells by Quarter in Current Calendar Year Office 2019
Novice
Conditionally Highlighting Blank Cells by Quarter in Current Calendar Year
 
Join Date: Nov 2019
Posts: 24
PrincessApril is on a distinguished road
Default

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
Reply With Quote
Reply

Tags
blank, conditional, current year

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Conditionally Highlighting Blank Cells by Quarter in Current Calendar Year 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
Conditionally Highlighting Blank Cells by Quarter in Current Calendar Year 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
Conditionally Highlighting Blank Cells by Quarter in Current Calendar Year How to call current PC date and/or current PC year KIM SOLIS Excel 2 11-04-2011 06:09 PM
Conditionally Highlighting Blank Cells by Quarter in Current Calendar Year Auto insert current month's name and current year Styler001 Word 4 01-25-2010 06:40 PM


All times are GMT -7. The time now is 03:59 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2019, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2019 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft