Biff,

You can't see how it does this though, it
evaluates straight through to the month number ......<<

Yes, I know. I also did "evaluate formula" before I posted. But try this.

=TEXT("JAN30","@") returns 38747 (left aligned) Is this text?
=MONTH(TEXT("JAN30","@")) returns 1.

Not sure how to interpret this.

This even works:

=MONTH("Jun1") = 6
=MONTH("Jun3500") = 6 <<

But this won't work.

=MONTH("Jan12006")

I think this is as far as I want to take it. Anyone interested in testing this with earlier version(s) or 2007?

What a difference a week makes! Last weekend I discovered how important it is to match data type when it comes to SUMPRODUCT. This week is the complete opposite. Excel is too temperamental or have I missed something?

Epinn

"T. Valko" wrote in message ...
Any comments on why MONTH(A1&1) works?

Hmmm............

That should not work but it does! You may have discovered something very
useful!!!!!!

If A1 = Jun (or June)

A1&1 is *definitely* a TEXT value: Jun1, but MONTH( ) seems to be able to
parse it as a date serial number. You can't see how it does this though, it
evaluates straight through to the month number but it obviously must.

This even works:

=MONTH("Jun1") = 6
=MONTH("Jun3500") = 6

This does not work:

=MONTH("Jan") = #VALUE!
=MONTH(Jan) = #NAME?

I've never seen this before. I've never seen anyone use this in a formula.
It appears to work. I hope I can remember this!

Biff

"Epinn" wrote in message
...
Thank you both for your response. I just felt that there had to be a date
function to take care of text. Believe me, I did check the list of date
functions but somehow I missed DATEVALUE. It was probably staring at me but
I was too sleepy and my eyes didn't open wide enough. On the bright side, I
got a bonus by posting. It is interesting that by using CEILING instead of
INT, I don't have to add 2.

All of the following formulae work. A1 = mmm

=IF(A1="","",CEILING(MONTH(DATEVALUE(A1&" 1"))/3,1))
=IF(A1="","",CEILING(MONTH(DATEVALUE(A1&"-1"))/3,1))
=IF(A1="","",CEILING(MONTH(DATEVALUE(A1&"-2006"))/3,1))
=IF(A1="","",CEILING(MONTH(DATEVALUE("1-"&A1&"-2006"))/3,1))

but =IF(A1="","",CEILING(MONTH(DATEVALUE(A1&"-1-2006"))/3,1)) returns
#VALUE.
I think d-mmm-yyyy is ISO date and mmm-d-yyyy is not recognized.

Now that I have found *the* date function, I realize that I don't really
need it to have my formula working. The following formula works just as
well for all 12 months.

=IF(A1="","",CEILING(MONTH(A1&1)/3,1))

See, I don't need to use DATEVALUE nor even any double quotes. Is it
"safe?" Oh, my God, I sound like you know who. I must have been
brainwashed. <G

This also works. =IF(A1="","",CEILING(MONTH(A1&" 1")/3,1))
Note: double quotes and space.

I don't quite understand. A1&1 or A1&" 1" are text and MONTH( ) has no
problem with that?? According to Help text MONTH has a syntax like this:
MONTH(serial number). Wonder what is happening here?

If I remember correctly, last night I also tried A1&" 1" and it returned
#VALUE. But then "nothing" worked last night. Today everything works
including the stuff that I don't expect to work.

Any comments on why MONTH(A1&1) works? Does it work for you too?

Thanks.

Epinn

"Epinn" wrote in message
...
Hi,

....... using date function(s)?

I have no problem converting a *date* to a quarter using the following
formula.

=IF(ISBLANK(C1),"",INT((MONTH(C1)+2)/3)) where C1 = 1/1/2006.

A blank cell is month 1 because it is treated as 1/1/1900. =month(C1)
returns 1 when C1 is blank. I think we have discussed this before. Hence,
I feel better checking for blanks, so that I won't get quarter 1 for a blank
cell.

If I key in a month as Jan, Feb ...... I can't use the above formula or any
other date functions. I can only think of using VLOOKUP, CHOOSE, etc., even
PivotTable (grouping) requires a date.

If I can convert Jan to 1 to 1/1/2006, then I can use the above formula
although VLOOKUP may be more direct.

Have I missed any date functions that may be able to do the job? Any ideas?

Thanks.

Epinn