Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 04-09-2020, 04:10 PM
Karen615 Karen615 is offline Conditional Format Dates Due in X Days Windows 7 64bit Conditional Format Dates Due in X Days Office 2010 64bit
Advanced Beginner
Conditional Format Dates Due in X Days
 
Join Date: Jun 2011
Location: Chicago
Posts: 71
Karen615 is on a distinguished road
Default Conditional Format Dates Due in X Days

I have a range of dates in A1:A100. I would like to set up conditional formatting in the cell to change to light red fill with dark text if the date is 3 days prior to that date in the cell.




So, if the date is 4/17/2020, I want it to conditionally format the date/cell on 4/14/2020, so I know that it's due in 3 days.



Your help is greatly appreciated.
Reply With Quote
  #2  
Old 04-10-2020, 03:41 PM
p45cal p45cal is offline Conditional Format Dates Due in X Days Windows 10 Conditional Format Dates Due in X Days Office 2019
Expert
 
Join Date: Apr 2014
Posts: 396
p45cal is a jewel in the roughp45cal is a jewel in the roughp45cal is a jewel in the roughp45cal is a jewel in the rough
Default

Select A1:A100
Ribbon: Home tab, Styles section, Conditional Formatting ,choose New Rule…
Choose Use a formula to determine which cells to format
Enter this formula:
=$A1-TODAY()=3
click the Format… button and format as you want it to appear
Click OK twice
Reply With Quote
  #3  
Old 04-12-2020, 04:54 PM
Karen615 Karen615 is offline Conditional Format Dates Due in X Days Windows 7 64bit Conditional Format Dates Due in X Days Office 2010 64bit
Advanced Beginner
Conditional Format Dates Due in X Days
 
Join Date: Jun 2011
Location: Chicago
Posts: 71
Karen615 is on a distinguished road
Default

Thank you very much for your help. It's much appreciated.



It looks as though this formula is formatting a cell if it is 3 days prior to whatever today's date may be. I actually could have several different dates in the range of cells and want it to change format if the date is 3 days prior to whichever date is in the cell. Not sure how this is done.



Your help is greatly appreciated.
Reply With Quote
  #4  
Old 04-13-2020, 03:10 AM
p45cal p45cal is offline Conditional Format Dates Due in X Days Windows 10 Conditional Format Dates Due in X Days Office 2019
Expert
 
Join Date: Apr 2014
Posts: 396
p45cal is a jewel in the roughp45cal is a jewel in the roughp45cal is a jewel in the roughp45cal is a jewel in the rough
Default

Quote:
Originally Posted by Karen615 View Post
so I know that it's due in 3 days
So this is wrong (the offering I made earlier)?:
(Can't attach a picture so a link)
Dropbox - 2020-04-13_105333.jpg
Reply With Quote
  #5  
Old 04-22-2020, 07:14 PM
Karen615 Karen615 is offline Conditional Format Dates Due in X Days Windows 7 64bit Conditional Format Dates Due in X Days Office 2010 64bit
Advanced Beginner
Conditional Format Dates Due in X Days
 
Join Date: Jun 2011
Location: Chicago
Posts: 71
Karen615 is on a distinguished road
Default

P45Cal,


Thank you again for your help.


Prior to applying the conditional formatting rule you gave me, I had another one applied to shade every other row. I used the following conditional formatting rule/formula: =MOD(Row(),2


Now because of my shading rule, the dates do not highlight on the shaded rows.



Do you know if there's a work-around so I'm able to use both rules?


Thank you in advance.
Reply With Quote
  #6  
Old 04-23-2020, 01:46 PM
p45cal p45cal is offline Conditional Format Dates Due in X Days Windows 10 Conditional Format Dates Due in X Days Office 2019
Expert
 
Join Date: Apr 2014
Posts: 396
p45cal is a jewel in the roughp45cal is a jewel in the roughp45cal is a jewel in the roughp45cal is a jewel in the rough
Default

Attach a sample workbook with this and I'll adjust the conditional formatting.
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Formula for calculating dates based on Business Days jthomas666 Excel 2 03-02-2020 09:05 AM
Default How to automatically Calculate number of days between two dates? wcngu1 Word 6 10-24-2019 04:20 AM
Conditional Format Dates Due in X Days increasing dates across college timetable by 40 days GavinHar Word Tables 2 03-15-2016 04:03 AM
Conditional Format Dates Due in X Days Calculating Quarterly Dates by Months not Days Artboy34 Excel 3 01-28-2016 09:47 AM
Start / End Dates in conjunction with man days loobs40 Project 5 09-19-2013 09:21 AM

Other Forums: Access Forums - Senior Forums

All times are GMT -7. The time now is 08:38 PM.


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