Date comparison

[Jonibenj]

I have a list of items which have expiry dates. I want these dates
compared to the current date every day, and some sort of formatting
applied when they are due this month, or overdue. I believe this can
be done with conditional formatting (which I have used before), but I
don't know how to construct the formula to compare each cell to the
present date.

Jonathan

 

[Guest]

use today() function to get current date.
use it in the conditional format box or use an extra column to flag the due
dates.
Say your date is in A1. Let B1 be IF(A1>=TODAY();"1";"").

"Jonibenj" escreveu:

 

[Ron Rosenfeld]

I have a list of items which have expiry dates. I want these dates
compared to the current date every day, and some sort of formatting
applied when they are due this month, or overdue. I believe this can
be done with conditional formatting (which I have used before), but I
don't know how to construct the formula to compare each cell to the
present date.

Jonathan

Format/Condtional Formatting

Cell Value Is: Less Than =TODAY()-30

as an example.


--ron

 

[Jonibenj]

What goes in the brackets after 'TODAY'? I can't make this work!

 

[Ron Rosenfeld]

What goes in the brackets after 'TODAY'? I can't make this work!

Nothing goes in the brackets after TODAY().

I don't know what you mean by "can't make this work".

Let me lead you through it step by step.

Try this on a new sheet:

A1: 1 Sep 2005

With A1 still selected, from the main menu select

Format/Conditional Formatting

In the first box of this dialog box select
Cell Value Is

In the adjacent box select
Less than

In the next adjacent box, enter the formula
=TODAY()-30

Then select Format
On the Font Tab select Bold; and the color Red

Then
OK
OK
should close the box and you should see A1 formatted in bold red.

You should be able to adapt this process to fit your needs. As written, the
cell contents will become RED and BOLD if the date in the box is more than 30
days before today. So if the date represents the DATE DUE, then it will turn
red when it is 30 days old.

Obviously you can change the condition if you want it flagged for other
circumstances.


--ron

 

[Jonibenj]

Thanks very much Ron, I'm a bit thick sometimes!

Jonathan

 

[Ron Rosenfeld]

Thanks very much Ron, I'm a bit thick sometimes!

Jonathan

It's not being thick. Just that sometimes it's much easier to see someone do
something, than to read about it. I have that problem, also.

But glad you've got the information you need.


--ron