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
|