How to convert a month to a quarter ......
Here's another one:
C1 = mmm
=IF(C1="","",CEILING(MONTH(DATEVALUE(C1&" 1"))/3,1))
Biff
"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
|