check date between 2 date values fails

[ali32j]

Hi All

I am trying to automate the building of a date in multiple cells based on the a year enterred into cell C3, using the following formula

="01/04"&"/"&C3

this will return 01/04/2009 if C3 has 2009 enterred. this works fine, but my only problem is, i am then usign a formula to check if a date falls between the dates created using above formula but it returns no value formula for check is as below

=IF(AND(C9 >= P7,C9 <= Q7), P9, "")

Where C9 is a date enterred by user (in this example date enterred is 03/04/2009), cell P7 and Q7 have the date creation formula used to create two different dates, for example

="01/04"&"/"&C3
="30/04"&"/"&C3

this returns an empty cell, my guess is this is because it is not excel date format??

i ve tried setting cell format to Date but it does not work, can anyone offer some guidance??

Thanks

Ali

 

[combo]

Date is a number formatted as date, you use text. You can build date with =DATE(C3,4,1)

combo

 

[SkipVought]

FYI: faq68-5827

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[ali32j]

THanks combo much appreciated