Thread: Conditional Highlighting When Approaching End of Quarter if Date in Cell is From Previous Quarter
View Single Post
 
Old 12-04-2019, 06:06 PM
p45cal's Avatar
p45cal p45cal is offline Windows 10 Office 2019
Expert
  
Join Date: Apr 2014
Posts: 871
p45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond repute
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