Function-Date Problems
 

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

Function-Date Problems
 

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

Function-Date Problems
 

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


--
Ron Coderre
------------------------------------------------------------------------
Ron Coderre's Profile: http://www.excelforum.com/member.php...o&userid=21419
View this thread: http://www.excelforum.com/showthread...hreadid=543470

Function-Date Problems
 

Thanks Miquel! Works Great!


--
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

Function-Date Problems
 

Ron Coderre Wrote:
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!


--
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