Excel 2003 Advanced Filter problem 1

[colinmitton]

I've setup an advanced filter to filter data between two dates. When I enter the dates in manually i.e.
Date Date
>=01/01/1970 <=23/06/2008

I filters the date correctly. I have a formula to give the date 3 months from todays date i.e. as today is the 23/09/3008 the cell with the formula gives the date 23/06/2008

When I change the advance filter to reference the cell with the formula it does not work.
Date Date
>=01/01/1970 <=G2

I have tried giving it a static reference, any ideas please?

Thanks

 

[xlbo]

Probably need to format the "G2" reference to be a date - at the mo, it is probably being viewed as the date serial number. Try:

="<=" & text(G2,"dd/mm/yyyy")

where dd/mm/yyyy is the format your dates are held in

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[colinmitton]

Excellent that worked, Thanks very much Geoff.

Can I just ask that the :
& Text(Cell Ref,"format")

That will then take the cell its referencing then apply the format required from that cell, then append to the current formula as text but in the correct format.

Just so I can figure it out in my own mind!

Thanks again.

 

[xlbo]

yup - that about covers it!

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[colinmitton]

Thanks very much again!