How to change cell fill based on date

[CAPtain232]

Looking for an easy way to highlight new additions to a spreadsheet. One column has the date for which the new entry is made. So I would like to use this date and maybe a formula to change cell fill color if the date in the cell is for example less than 15 days....

Any ideas on an easy way to do this? Conditional formatting?

 

[Joe4]

Yes, you can use Conditional Formatting.

If you need help with that, please lay out an actual example for us, letting us us know the cell addresses, cell values, and rule that you want.

 

[CAPtain232]

So I need to change the fill in the cell if the date is less than 15 days old. This would apply to all cells in column D. The intent is to let me know when someone has added a new item within the last 15 days

....A..............B..................................C.................D.................E....................F.........

1	72" Braner guarding needs to be installed around the scrap winders	Jared R/Jeff C	02/01/18	4/1/2018
1	Uncoiler snubber looses pressure if the uncoiler is disabled. This could cause injury while banding pup coils	Jeff C	2/1/2018	2/9/2018
2	Track Mobile repairs	Jeff C	10/19/17	1/1/2018
2	Crane proximity lights for building one cranes	Jeff C	2/1/2018	12/31/2018
2	Install a disable feature on the drive side for the tension pad and hold down roll.	Jeff C	02/01/18	4/1/2018
2	Recoiler will expand and collapse while recoiler area is disabled.	Jeff C	02/01/18	4/1/2018	03/02/18
2	Mirrors need to be installed on NP8	Jeff C	02/01/18	4/1/2018	03/15/18
2	1/4" Slitter the outer rings of the 24" ID sleeve, Plates 2 and 5 can onlyhave one bolt placed in each plate. These plates are heavyand are a crush hazard.	Jeff c	02/21/18	5/1/2018	03/14/18
2	Slear Operators have to reach up into the rollers and pull the trim down so it can be tied to the scrap winder.	Jared R/Jeff C	03/19/18
2	Shear head flying fault- shear head moves very fast and on its own while employees are very close. This could cause an injury to an employee.	Jeff C	03/19/18
2	Install the modules in retrofitting the crane to be controlled by a wireless remote.	Jeff C	01/03/2018	03/01/2018
3	Crane safety latches need to be replaced	Jeff C	02/01/18	12/31/2018
3	Order chain lifting devices to get away from nylon straps	Jared R/Jeff C	2/1/2018	4/1/2018
3	Fix the break on the recoiler so that it holds at every gauge with out being adjusted.	Jeff C	02/01/18	4/1/2018	03/15/18
3	Slear remotes/pendants to make them more operator friendly (wireless remotes)	Jared R/Jeff C	02/01/18	4/1/2018

<tbody>
</tbody>

 

[Joe4]

So, it looks like you want to highlight column B based on the dates in column D.
Select column B, then enter this Conditional Formatting:

=D1 > Today()-15

and choose your yellow formatting color.

 

[CAPtain232]

So here is the formula that I put in

=$D$9:$D$307 > TODAY()-15

With this formula, nothing in column B was highlighted and the conditional formatting is applied to the cells in column B

When I change the formula to =$D$9:$D$307 < TODAY()-15, EVERYTHING in column B is highlighted

I have column D formatted as DATE ex 3/23/18

Not sure why formula 1 is not working and not sure why formula 2 highlights everything

 

[jtakw]

Hi,

You Don't need to change the formula provided by Joe4 in Post #4 , Just select the range in Column B, if your data starts in B1, use Joe4's formula as posted, if your data starts in B23, then change the formula to =D23 > Today()-15
Excel will automatically include the "range" as you selected.

 

[CAPtain232]

I have been playing with it and it almost seems as if the "CLOCK" is off. Is it possible that EXCEL can be confused as to what TODAY's date actually is? My computer shows 3.23.2018

I have redone the formula and it appears to maybe work, but its as if EXCEL is referencing some oddball date

 

[CAPtain232]

THANK YOU JOE4!!! and jtakw!!!


My own mistake... I need to reference D9 as the first cell in column D not D1. Appears to be working properly

 

[jtakw]

You're welcome, glad you got it working.