Cell Conditions

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a spreadsheet listing members of staff, dates they have attended
training and their status regarding their training. They are either
operational or suspended. Conditional formatting will only allow me to
change the font of text in the cell. Is there a function that will
automatically change the text depending on different conditions ie once a
date has expired the cell changes from operational to suspended automatically?
 
Let's say the dates attended are in column B and we want status in column C.
Just for this example, I am assuming that after one year (365 days) the
training is expired.

if C1 enter:
=IF(TODAY()-B1>365,"suspended","operational")
and copy down

Naturally you will adjust the 365 to meet your criteria.
 
Is there a function that will
automatically change the text depending on different conditions ie once a
date has expired the cell changes from operational to suspended automatically?
Click to expand...

I dont know the specific conditions you wish to apply here but
here is an example formula that should get you in the right direction.

=IF(MONTH(A1)<>2,"Operational","Suspended")

This formula relies on a date being entered into cell A1. If the
month in that cell is not equal to February, then the cell will
display "Operational". If it is = to February, then the cell will
display "Suspended". If you have any trouble adapting this to your
needs, post again or email me, I'll be glad to help.

Mark
 
Just another thought ..

Assuming dates in B2 down, try in C2:
=IF(B2="","",IF(TODAY()>=DATE(YEAR(B2)+1,MONTH(B2),DAY(B2)),"suspended","operational"))
Copy C2 down
 
Back
Top