formulas/function

[craig@help]

Hi there!

Im wondering if you could help me out as im trying all kind of tricks to make what i want it to work, i have tried =if(or(a1>b1,a1<b2), "x","y") function, (b1=01/01/2005, b2= 31/12/2005) it will work but if no date has been input then it still show x

'm looking for fornulas/function that will help me with my work, see below:
between 01/01/2005 and 31/12/2005 = "x"
between 01/01/2006 and 31/12/2006="Y"
if no date input then ignore it or leave it blank.

I would be grateful for your kind support.

Craig

[Sloth]

=IF(ISBLANK(A1),"",IF((DATE(2005,1,1)<=A1)*(DATE(2005,12,31)>=A1),"X",IF((DATE(2006,1,1)<=A1)*(DATE(2006,12,31)>=A1),"Y","Out of Range")))

If your dates include times (example: 12/31/2005 4:30 PM), then you might
need this formula...

=IF(ISBLANK(A1),"",IF((DATE(2005,1,1)<=A1)*(DATE(2006,1,1)>A1),"X",IF((DATE(2006,1,1)<=A1)*(DATE(2007,1,1)>A1),"Y","Out of Range")))

"craig@help" wrote:

>
> Hi there!
>
> Im wondering if you could help me out as im trying all kind of tricks
> to make what i want it to work, i have tried =if(or(a1>b1,a1<b2),
> "x","y") function, (b1=01/01/2005, b2= 31/12/2005) it will work but if
> no date has been input then it still show x
>
> 'm looking for fornulas/function that will help me with my work, see
> below:
> between 01/01/2005 and 31/12/2005 = "x"
> between 01/01/2006 and 31/12/2006="Y"
> if no date input then ignore it or leave it blank.
>
> I would be grateful for your kind support.
>
> Craig
>
>
>
> --
> craig@help
> ------------------------------------------------------------------------
> craig@help's Profile: http://www.excelforum.com/member.php...o&userid=36201
> View this thread: http://www.excelforum.com/showthread...hreadid=559885
>
>

[Ardus Petus]

IF(A1="","",IF(YEAR(A1)=2005,"X",IF(YEAR(A1)=2006,"Y")))

HTH
--
AP

"Sloth" <[email protected]> a écrit dans le message de news:
[email protected]...
> =IF(ISBLANK(A1),"",IF((DATE(2005,1,1)<=A1)*(DATE(2005,12,31)>=A1),"X",IF((DATE(2006,1,1)<=A1)*(DATE(2006,12,31)>=A1),"Y","Out
> of Range")))
>
> If your dates include times (example: 12/31/2005 4:30 PM), then you might
> need this formula...
>
> =IF(ISBLANK(A1),"",IF((DATE(2005,1,1)<=A1)*(DATE(2006,1,1)>A1),"X",IF((DATE(2006,1,1)<=A1)*(DATE(2007,1,1)>A1),"Y","Out
> of Range")))
>
> "craig@help" wrote:
>
>>
>> Hi there!
>>
>> Im wondering if you could help me out as im trying all kind of tricks
>> to make what i want it to work, i have tried =if(or(a1>b1,a1<b2),
>> "x","y") function, (b1=01/01/2005, b2= 31/12/2005) it will work but if
>> no date has been input then it still show x
>>
>> 'm looking for fornulas/function that will help me with my work, see
>> below:
>> between 01/01/2005 and 31/12/2005 = "x"
>> between 01/01/2006 and 31/12/2006="Y"
>> if no date input then ignore it or leave it blank.
>>
>> I would be grateful for your kind support.
>>
>> Craig
>>
>>
>>
>> --
>> craig@help
>> ------------------------------------------------------------------------
>> craig@help's Profile:
>> http://www.excelforum.com/member.php...o&userid=36201
>> View this thread:
>> http://www.excelforum.com/showthread...hreadid=559885
>>
>>