Function-Date Problems

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Brian Matlack - 18 May 2006 21:23 GMT

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

Signature

Brian Matlack

Reply to this Message

Miguel Zapico - 18 May 2006 21:36 GMT

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.

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

Reply to this Message

Brian Matlack - 18 May 2006 22:04 GMT

Thanks Miquel! Works Great!

Signature

Brian Matlack

Reply to this Message

Ron Coderre - 18 May 2006 21:38 GMT

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

Signature

Ron Coderre

Reply to this Message

Brian Matlack - 18 May 2006 22:07 GMT

Ron Coderre Wrote:

> Hi, Brian
>
[quoted text clipped - 11 lines]
> Regards,
> Ron

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

Signature

Brian Matlack

Reply to this Message