Function-Date Problems

B

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

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

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
 
B

Brian Matlack

Ron said:
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
Click to expand...

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