Date conversion into Quarters in excel

[willisd]

Hi
I want to add an OR/IF formula that allows me to return a value (in this case, quarter) once I specify the month. I can do the first part (below) but I cant complete the forumula so that if Feb/Mar/April return Q2, if May/Jun/July return Q3 etc.

Can somebody help me write the full formula thanks

=IF(OR(A5="january", A5="february", A5="march"),"Q1","")

 

[AlanY]

try this

=LOOKUP(MONTH(DATEVALUE(A5&" 1")),{1,4,7,10},"Q"&{1,2,3,4})

 

[willisd]

hi thanks for your help. I tried and id didn't work. Is there anyway that you can keep it a long the lines of my formula? I just struggle with nested if and or statements and the number of brackets I have to use Many thanks if you can

 

[AlanY]

not sure why it didn't work for you.

to extend your formula to work,

=IF(OR(A7="january",A7="february",A7="march"),"Q1",IF(OR(A7="april",A7="may",A7="june"),"Q2",IF(OR(A7="july",A7="august",A7="september"),"Q3","Q4")))

 

[willisd]

you are a legend! thanks for all your help. Its much appreciated.
Dan

 

[AlanY]

you're welcome