Expiration dates
 

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

Expiration dates
 

When will the course expire?

Assume 180 days after completion.

FormatCFFormula is =cellref<TODAY()-180


Gord Dibben MS Excel MVP

On Wed, 8 Jul 2009 13:28:00 -0700, Deeno
wrote:

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

Expiration dates
 

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

When will the course expire?

Assume 180 days after completion.

FormatCFFormula is =cellref<TODAY()-180


Gord Dibben MS Excel MVP

On Wed, 8 Jul 2009 13:28:00 -0700, Deeno
wrote:

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

Expiration dates
 

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

On Wed, 8 Jul 2009 14:41:02 -0700, Deeno
wrote:

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

When will the course expire?

Assume 180 days after completion.

FormatCFFormula is =cellref<TODAY()-180


Gord Dibben MS Excel MVP

On Wed, 8 Jul 2009 13:28:00 -0700, Deeno
wrote:

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

Expiration dates
 

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


"Gord Dibben" wrote:

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

On Wed, 8 Jul 2009 14:41:02 -0700, Deeno
wrote:

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

When will the course expire?

Assume 180 days after completion.

FormatCFFormula is =cellref<TODAY()-180


Gord Dibben MS Excel MVP

On Wed, 8 Jul 2009 13:28:00 -0700, Deeno
wrote:

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

Expiration dates
 

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.

FormatNew RuleFormula 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


On Wed, 8 Jul 2009 15:40:04 -0700, Deeno
wrote:

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


"Gord Dibben" wrote:

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

On Wed, 8 Jul 2009 14:41:02 -0700, Deeno
wrote:

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

When will the course expire?

Assume 180 days after completion.

FormatCFFormula is =cellref<TODAY()-180


Gord Dibben MS Excel MVP

On Wed, 8 Jul 2009 13:28:00 -0700, Deeno
wrote:

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

Expiration dates
 

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

"Gord Dibben" wrote:

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.

FormatNew RuleFormula 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


On Wed, 8 Jul 2009 15:40:04 -0700, Deeno
wrote:

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


"Gord Dibben" wrote:

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

On Wed, 8 Jul 2009 14:41:02 -0700, Deeno
wrote:

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

When will the course expire?

Assume 180 days after completion.

FormatCFFormula is =cellref<TODAY()-180


Gord Dibben MS Excel MVP

On Wed, 8 Jul 2009 13:28:00 -0700, Deeno
wrote:

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

Expiration dates
 

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

On Wed, 8 Jul 2009 16:21:01 -0700, Deeno
wrote:

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

"Gord Dibben" wrote:

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.

FormatNew RuleFormula 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


On Wed, 8 Jul 2009 15:40:04 -0700, Deeno
wrote:

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


"Gord Dibben" wrote:

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

On Wed, 8 Jul 2009 14:41:02 -0700, Deeno
wrote:

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

When will the course expire?

Assume 180 days after completion.

FormatCFFormula is =cellref<TODAY()-180


Gord Dibben MS Excel MVP

On Wed, 8 Jul 2009 13:28:00 -0700, Deeno
wrote:

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