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