View Single Post
  #3   Report Post  
Posted to microsoft.public.excel.newusers
Don Guillett Don Guillett is offline
external usenet poster
Posts: 10,124
Default How to convert a month to a quarter ......

Try incorporating this
=MONTH(DATEVALUE(C1& "-2006"))

Don Guillett
SalesAid Software

"Epinn" wrote in message

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

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

=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

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?