Trouble with my Date Alert Fomula

Copper Contributor

Good Afternoon.

 

I'm trying to create a formula that will alert me in an excel sheet when a date is 30 days or sooner.

 

In my sheet, I have I have several date ranges such as :

 

2/5/19

3/5/19

4/5/19

 

Ideally I want it to put a notation by any date that is 30 days away from today.

 

I was using this formula here =IF(D4=(TODAY()+$G$3),"<<<","") which works for precise date range, but is there a way to make it less than or = to the date? (D4 being the date range, $G$3 being the number of days out from when I need to be alerted. So if I need a 30 day timer or a 3 day timer I can put it in there and it will show me with <<<.)

 

6 Replies

Do you mean

=IF(D4<=(TODAY()+$G$3),"<<<","")

?

 

This unfortunately does prior date range + or -. So example 

 

2/3/19

2/4/19

/2/5/19

 

Where $G$3 = 4 it would mark all 3 days with that range.

I only need it to mark upcoming dates, if that makes sense.

If only upcoming days when like

image.png

assuming today is Feb 05. Correct?

Exactly, 

 

So in my spreadsheet I have a column with dates in it, and to the right of the column I need a way to mark if those dates are coming up in the next X amount of days, but with my formula I made, it only does exact dates. I tried to use <= with it to do 3 days or less, but then it marks 3 days prior to today as well.

 

 

@JakeZimmerman , for such data you may apply conditional formatting rule

image.png

with formula

=($D4<(TODAY()+$G$4))*($D4>TODAY())

and applying custom format to dates as

mm/dd/yy"<<<"

Or use only mark in next column with formula

=IF(($D4<(TODAY()+$G$4))*($D4>TODAY()),"<<<","")

File is attached