Notify if the date expires in Excel DB

[eXtremer]

Hi all.

There is a excel database with columns, there is a column with dates and I need to be notified by excel somehow that it past for example 30 days from that date, is it possible to implement such a thing in excel ? I need to be notified just once, after that to click on it and the reminder has to be set OFF.

I really need to find a workaround.

Thank you in advance.

 

[eXtremer]

Am I doing something wrong ?

 

[Ruddles]

Am I doing something wrong ?

I don't know - that's all I can see of your post. Was there more?

 

[eXtremer]

I don't know - that's all I can see of your post. Was there more?

Yes, a screenshot:

image link

 

[Ruddles]

Ah, that works!

Yes, CheckExpiryDates goes in a new general code module, not a worksheet code module. In VBA, go Insert > Module and put it there.

 

[eXtremer]

Thanks Ruddles, some feedback...

1. If I change the DATE_COL to a column with dates expired long time ago, for example 2007, 2008...I'm still being notified. That column has dates from 2011 too, the dates that I need, but it shows me from all the years.

"Policy 111111 expires on 10.01.2009"

 

[Ruddles]

Change the test so that in addition to checking whether the expiry date is before thirty days time, it's also in the future:-

If .Cells(iRow, DATE_COL) < Now() + ALERT_PERIOD [COLOR=red][B]And .Cells(iRow, DATE_COL) > Now()[/B][/COLOR] Then

Try that and report back?

 

[eXtremer]

Change the test so that in addition to checking whether the expiry date is before thirty days time, it's also in the future:-

If .Cells(iRow, DATE_COL) < Now() + ALERT_PERIOD [COLOR=red][B]And .Cells(iRow, DATE_COL) > Now()[/B][/COLOR] Then

Try that and report back?

It works! Thank you.

About Conditional Formatting "=A1<(TODAY()+10)"...can't get it work, the cell doesn't change it's colour.

 

[Ruddles]

You're using the cell reference of the cell with the date in it rather than A1, yes?

 

[eXtremer]

You're using the cell reference of the cell with the date in it rather than A1, yes?

Really can't understand how it works, if I manually choose cells with no data, it sets itself red. If I choose cells the the expiry dates is does nothing, but everything manually, doesn't it have to be automatically ?

 

[Ruddles]

Yes, conditional formatting changes cell colours instantly. If it's not happening then you've made a mistake.

Get it working on a single cell first, then copy the formatting to all the other cells you want it to apply to.

Incidentally, that formula will set empty cells to red so you might want to use something like this instead:-

=AND(A1>0,A1<(TODAY()+10))

or:-

=AND(A1>TODAY(),A1<(TODAY()+10))