View Single Post
  #5   Report Post  
Posted to microsoft.public.excel.newusers
Teethless mama Teethless mama is offline
external usenet poster
 
Posts: 3,718
Default How to convert a month to a quarter ......

=IF(C1="","",ROUNDUP(MONTH(C1)/3,0))


"Epinn" wrote:

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