IF formula To Convert Months To Quarters

I created the following formula to convert a field with months (G:G) to what quarter of the year it falls in.

Formula:

Please Login or Register  to view this content.

It doesn't work, how do I get it to work?



*Edit/Correction: I should probably check to see if the formula works in all the cells I want it to first.

If G2 contains the month name as a TEXT entry...

=CEILING(MONTH(1&G2)/3,1)

with VLookup in a table.

put the months in column A and the quarter i column B

Please Login or Register  to view this content.

Originally Posted by Craig K.

Tony, I just reverse engineered it and that formula is beautiful. I hate using formulas I don't understand.

Thank You!

Me too!

I do the same thing when I see something different. I "slice it and dice it" until I get it then I test it under all kinds of scenarios to see how it reacts.

This is how we learn.

Thanks for the feedback!

I have many years of experience with Excel and I do tons of experimentation trying different things.

There must be something wrong with me because I love this stuff!

I'd go with Tony's suggestion but just in the interests of learning......

Your original formula will work OK with ORs, i.e.

=IF(OR(G2={"Jan","Feb","Mar"}),1,IF(OR(G2={"Apr","May","Jun"}),2,IF(OR(G2={"Jul","Aug","Sep"}),3,4)))

For an IF function to work as required the "test" should normally give you a single TRUE/FALSE answer but when you use G2={"Jan","Feb","Mar"} that returns an "array" of TRUE/FALSE values, e.g. if G2 = "Feb" you get {FALSE,TRUE,FALSE}. In your formula you want TRUE if any of those is TRUE so that's an OR, either of these will give you a single TRUE/FALSE

=OR(G2={"Jan","Feb","Mar"})

or

=OR(G2="Jan",G2="Feb",G2="Mar")