 • 662K Members
• 5,803 Online
• 813K Conversations

Highlighted

# Trouble with my Date Alert Fomula

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

# Re: Trouble with my Date Alert Fomula

Do you mean

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

?

Highlighted

# Re: Trouble with my Date Alert Fomula

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

# Re: Trouble with my Date Alert Fomula

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

Highlighted

# Re: Trouble with my Date Alert Fomula

If only upcoming days when like assuming today is Feb 05. Correct?

Highlighted

# Re: Trouble with my Date Alert Fomula

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

# Re: Trouble with my Date Alert Fomula

@JakeZimmerman , for such data you may apply conditional formatting rule 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