Microsoft Office Forums Conditional Highlighting When Approaching End of Quarter if Date in Cell is From Previous Quarter

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 12-04-2019, 10:50 AM
PrincessApril PrincessApril is offline Conditional Highlighting When Approaching End of Quarter if Date in Cell is From Previous Quarter Windows 10 Conditional Highlighting When Approaching End of Quarter if Date in Cell is From Previous Quarter Office 2019
Novice
Conditional Highlighting When Approaching End of Quarter if Date in Cell is From Previous Quarter
 
Join Date: Nov 2019
Posts: 23
PrincessApril is on a distinguished road
Default 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)))
Am I on the right track, and if so, what might be causing the error? Thanks in advance for any assistance!
Reply With Quote
  #2  
Old 12-04-2019, 06:06 PM
p45cal p45cal is online now Conditional Highlighting When Approaching End of Quarter if Date in Cell is From Previous Quarter Windows 10 Conditional Highlighting When Approaching End of Quarter if Date in Cell is From Previous Quarter Office 2019
Expert
 
Join Date: Apr 2014
Posts: 314
p45cal has a spectacular aura aboutp45cal has a spectacular aura aboutp45cal has a spectacular aura about
Default

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))
Reply With Quote
  #3  
Old Yesterday, 04:28 PM
PrincessApril PrincessApril is offline Conditional Highlighting When Approaching End of Quarter if Date in Cell is From Previous Quarter Windows 10 Conditional Highlighting When Approaching End of Quarter if Date in Cell is From Previous Quarter Office 2019
Novice
Conditional Highlighting When Approaching End of Quarter if Date in Cell is From Previous Quarter
 
Join Date: Nov 2019
Posts: 23
PrincessApril is on a distinguished road
Default

Quote:
Originally Posted by p45cal View Post
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).
Reply With Quote
  #4  
Old Today, 04:25 AM
p45cal p45cal is online now Conditional Highlighting When Approaching End of Quarter if Date in Cell is From Previous Quarter Windows 10 Conditional Highlighting When Approaching End of Quarter if Date in Cell is From Previous Quarter Office 2019
Expert
 
Join Date: Apr 2014
Posts: 314
p45cal has a spectacular aura aboutp45cal has a spectacular aura aboutp45cal has a spectacular aura about
Default

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)))
Reply With Quote
  #5  
Old Today, 07:37 AM
PrincessApril PrincessApril is offline Conditional Highlighting When Approaching End of Quarter if Date in Cell is From Previous Quarter Windows 10 Conditional Highlighting When Approaching End of Quarter if Date in Cell is From Previous Quarter Office 2019
Novice
Conditional Highlighting When Approaching End of Quarter if Date in Cell is From Previous Quarter
 
Join Date: Nov 2019
Posts: 23
PrincessApril is on a distinguished road
Default

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?
Reply With Quote
  #6  
Old Today, 12:12 PM
p45cal p45cal is online now Conditional Highlighting When Approaching End of Quarter if Date in Cell is From Previous Quarter Windows 10 Conditional Highlighting When Approaching End of Quarter if Date in Cell is From Previous Quarter Office 2019
Expert
 
Join Date: Apr 2014
Posts: 314
p45cal has a spectacular aura aboutp45cal has a spectacular aura aboutp45cal has a spectacular aura about
Default

A cursory bit of experimentation suggests yes; why don't you try it?
Reply With Quote
Reply

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
Conditional Highlighting When Approaching End of Quarter if Date in Cell is From Previous Quarter 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
Conditional Highlighting When Approaching End of Quarter if Date in Cell is From Previous Quarter 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


All times are GMT -7. The time now is 12:23 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