Date calculations

  • Thread starter Thread starter Mothra
  • Start date Start date
M

Mothra

I'm getting a bit stuck with date calculations. I'm trying to make a cell
that changes colour when it expires, but I can't seem to get the right
syntax for the cell formula. This is kind of what I'm trying to acheive,
but I'm rather new to Excel formulae and the help file doesn't have very
many useful examples.

=if((DATE_VALUE(NOW)) > (DATE_VALUE("12/12/2002"), "expired", "time left")

BTW, I'm not sure how to change the cell colour either.
 
Hello Mothra,

You were very close with your formula, but change it to:

=IF(TODAY()>DATEVALUE("12-12-2002"),"expired","time left")

Because 12-12-2002 is a textual representation of a date, you use DATEVALUE
to translate it to the number that represents this specific day. You do not
have to do this for the present date (TODAY) or the present date+time (NOW),
as these are Excel functions that already work with the datevalue-numbers,
behind the screens.

You may have to change 12-12-2002 back to 12/12/2002. This depends on
Excel's nationalization.

To change the cell-color according to the result of the formula:

Select the cell> go to format (5th menu from left on the menu
bar)>conditional format

and specify that when the cell value is exactly [expired] (without
brackets), then font, border or fill color will be as you set is. Look
around in the conditional fomat pane and you'll ge the hang of it soon
enough.

HTH

Have a lovely day.
 
Back
Top