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