Comparing dates

D

Debbie F

Hi

I have a date in A5 and I need to write a formula in an adjacent column that
checks the date and if the date is less than 3 months old to display it in
another colour, if it is more than 3 months old to do nothing.

I am not sure which date function to use. Can anyone help?

Thanks
 
M

MS Office

=MONTH(TODAY())-MONTH(A5)

The above formula will calculate the difference ( in number of months)
between today's date and the date in A5.
You may then apply conditional formatting.
 
B

Biff

Hi!

Select cell A5

Goto Format>Conditional Formatting
Select Formula IS
Enter this formula:

=AND(ISNUMBER(A5),A5>DATE(YEAR(TODAY()),MONTH(TODAY())-3,DAY(TODAY())))

Click the Format button and select the style(s) you want
OK out

Biff
 
D

Debbie F

Thanks very much

MS Office said:
=MONTH(TODAY())-MONTH(A5)

The above formula will calculate the difference ( in number of months)
between today's date and the date in A5.
You may then apply conditional formatting.
Click to expand...
 
B

Biff

Hi!

Today = Sept 7 2005
A5 = Sept 30 2004

Your formula would return 0 and since zero is less than 3 the wrong format
would be applied.

Another thing to consider is how do you measure a month? 29 days? 30 days?
31 days?

Biff
 
R

Ron Rosenfeld

=MONTH(TODAY())-MONTH(A5)

The above formula will calculate the difference ( in number of months)
between today's date and the date in A5.
You may then apply conditional formatting.
Click to expand...

It will only do this if you require that both months be in the same year.

Try testing it by substituting 5 Jan 2006 for today and A5: 1 Sep 2005


--ron
 
R

Ron Rosenfeld

On Tue, 6 Sep 2005 20:59:01 -0700, "Debbie F" <Debbie
Hi

I have a date in A5 and I need to write a formula in an adjacent column that
checks the date and if the date is less than 3 months old to display it in
another colour, if it is more than 3 months old to do nothing.

I am not sure which date function to use. Can anyone help?

Thanks
Click to expand...

Which cell do you wish to have displayed in red? (In other words, what do you
mean by "it")?

If the cell in which you want to have this formula:

=DATEDIF(A5,TODAY(),"m")

Format/Cells/Number/Custom Type: [Red][<3]0;General

If you want to have A5 with the different colors, then

Select A5
Format/Conditional Formatting
Formula Is: =datedif(a5,today(),"m")<3
then format to taste.

Note that in your description, you do not indicate what you want to happen if
the date IS three months old.


--ron
 
Top