Home

Trouble with my Date Alert Fomula

Highlighted
New 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
Highlighted

Do you mean

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

?

 

Highlighted

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.

Highlighted

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

Highlighted

If only upcoming days when like

image.png

assuming today is Feb 05. Correct?

Highlighted

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.

 

 

Highlighted

@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