Comparing dates

[Guest]

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

 

[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.

 

[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

 

[David McRitchie]

no it will not, and left out how 3 months is to be worked in anyway.

would suggest looking at Chip Pearson's page
http://www.cpearson.com/excel/datedif.htm

 

[Guest]

Thanks very much

=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.

 

[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

 

[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.

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

 

[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

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