Expiration dates

D

Deeno

I have a spreadsheet where I have inputed dates (mm/dd/yyyy) when training
classes were completed. I am trying to create some way of notifying user
(conditional formating?) that the course is expired, but cannot seem to get
it to work.

The sheet is set up in this format:

Name Class1 Class2 etc
John Date Date etc
bob date etc etc
etc etc etc etc
 
G

Gord Dibben

When will the course expire?

Assume 180 days after completion.

Format>CF>Formula is =cellref<TODAY()-180


Gord Dibben MS Excel MVP
 
D

Deeno

Expiration is a year. I tried =cellref<TODAY()-360 and it did not seem to do
anything. I highlighted the applicable cells, went to Conditional
Formating(CF?)>New Rule...>Use a formula to determine which cells to format
and imputed the formula into the "Format values where this formula is true:"
 
G

Gord Dibben

First select all the cells to format.

Assuming you changed "cellref" to a real reference like A1

I don't know why your formatting is not working.

Make sure Excel does not add any $ signs to the reference.


Gord
 
D

Deeno

ahhh cellref is a specific cell. Sorry. What am I supposed to reference it to
though?
 
G

Gord Dibben

You don't reference it to anything but itself and the function TODAY() which
returns the current date and updates every day.

Using your example data below I would say select B2:D4 which would be
completion dates.

Format>New Rule>Formula is: =B2<TODAY()-365

The B2 will increment across the range as a relative cell reference.

So each cell in the range will refer to itself.

A problem that occasionally happens is Excel changes the cell reference to
an Absolute by adding $ signs

$B$2 or similar which will anchor the reference to one cell............you
don't want that.



Gord
 
G

Gord Dibben

Check the rules for C to K

Remove any absolute reference $ signs if any.

If none found and still no joy, send the workbook to me by email at

gorddibbATshawDOTca

Make the needed changes to AT and DOT


Gord
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top