Expiration dates

[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

 

[Gord Dibben]

When will the course expire?

Assume 180 days after completion.

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


Gord Dibben MS Excel MVP

 

[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:"

 

[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

 

[Deeno]

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

 

[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 B24 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

 

[Deeno]

Success!....for collumn B, but the formula is not working for C-k...wierd.

 

[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