Datevalue

[paschalleddie1]

I want to replace "1/1/05" and "3/31/05" in the below formula with a
Date Function so that the formula will automatically update for the
current year, ie, 1/1/05, 1/1/06, 1/1/07, etc.


{=SUM(SUMIF($A$22:$A$200,{">=1/1/05","<=3/31/05","<>"},B22:B*200)*{1,1,-1})}


Thanks

[AlfD]

Hi!

You can generate a string with the year appended using

="1/1/"& RIGHT(YEAR(TODAY()),2)

which then reads 1/1/05. (well, it does this year)

Alf

[paschalleddie1]

Dear Alf,

When I edited the formula as follows, the formula result changed from when I had 1/1/05 and 3/31/05 enterd. The serial numbers for the dates have somehow changed. I could not enter the parenthesis as shown, "1/1/" and "3/31/", without getting an error. Have I done this correctly?

Thanks

{=SUM(SUMIF($A$22:$A$200,{">=1/1/& RIGHT(YEAR(TODAY()),2)","<=3/31/& RIGHT(YEAR(TODAY()),2)","<>"},B22:B200)*{1,1,-1})}


Your Reply:
="1/1/"& RIGHT(YEAR(TODAY()),2)

Original:
{=SUM(SUMIF($A$22:$A$200,{">=1/1/05","<=3/31/05","<>"},B22:B*200)*{1,1,-1})}

[Domenic]

Wouldn't the following formula do?

=SUMPRODUCT(--($A$22:$A$200>=DATE(YEAR(TODAY()),1,1)),--($A$22:$A$200<=DATE(YEAR(TODAY()),3,31)),$B$22:$B$200)

I want to replace "1/1/05" and "3/31/05" in the below formula with a
Date Function so that the formula will automatically update for the
current year, ie, 1/1/05, 1/1/06, 1/1/07, etc.


{=SUM(SUMIF($A$22:$A$200,{">=1/1/05","<=3/31/05","<>"},B22:B*200)*{1,1,-1})}


Thanks

[paschalleddie1]

Dear Domenic,

Thank you immensely! Problem is solved!