Function-Date Problems

[Brian Matlack]

Hi!
How can I convert a date like this 01/12/06 into one that looks like this Jan-06 so that a sumif or lookup will get all dates that fall in January of 2006. Formatting does not do it because the functions still reads it as 01/12/06.
Any help or direction would be greatly appreciated!! Thanks!!

[Ron Coderre]

Hi, Brian

Try this....

For dates in A1:A20 and Amounts in B1:B20

C1: =SUMPRODUCT((TEXT(A1:A20,"MMM-YY")="Jan-06")*B1:B20)

That formula returns the sum of the Col_B values where the Col_A date is in the month of January 2006.

Does that help?

Regards,
Ron

[Miguel Zapico]

If you want to check just for the month, this formula will give you always
the first day of the month for the given date:
=DATE(YEAR(A1),MONTH(A1),1)
You can build your lookup table, or sumif conditions, with that date.

Hope this helps,
Miguel.

"Brian Matlack" wrote:

>
> Hi!
> How can I convert a date like this 01/12/06 into one that looks like
> this Jan-06 so that a sumif or lookup will get all dates that fall in
> January of 2006. Formatting does not do it because the functions still
> reads it as 01/12/06.
> Any help or direction would be greatly appreciated!! Thanks!!
>
>
> --
> Brian Matlack
> ------------------------------------------------------------------------
> Brian Matlack's Profile: http://www.excelforum.com/member.php...fo&userid=3508
> View this thread: http://www.excelforum.com/showthread...hreadid=543470
>
>

[Brian Matlack]

Thanks Miquel! Works Great!

[Brian Matlack]

Hi, Brian

Try this....

For dates in A1:A20 and Amounts in B1:B20

C1: =SUMPRODUCT((TEXT(A1:A20,"MMM-YY")="Jan-06")*B1:B20)

That formula returns the sum of the Col_B values where the Col_A date is in the month of January 2006.

Does that help?

Regards,
Ron

Thanks Ron! This will help in another area of my spreadsheet as well!