Home

Trouble with my Date Alert Fomula

JakeZimmerman
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

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

Related Conversations
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
Edge insider Dev bypasses IE mode website list
HotCakeX in Enterprise on
4 Replies