Need a Formula to evaluate for Fiscal Quarter

Ima_learnin'

Active Member
Joined
Dec 14, 2002
Messages
266
Hello All,
I have JAN,FEB,MAR…..NOV,DEC in range D7:D18…..I’m stumped…Along E7:E18…I need a formula that evaluates if the cell value is JAN, FEB or MAR return 1st Quarter, if the cell value is APR, MAY, or JUN return 2nd Quarter…so one and so forth until 4th quarter.

As always thanks!!
Ima Learnin’
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

rrdonutz

Well-known Member
Joined
Jan 15, 2003
Messages
564
If you really want a formula, here's one approach:

="Quarter "&INT((MONTH(TEXT(D7&1,"mmm-dd"))-1)/3)+1

although Alan's suggestion of a lookup table is probably the better way to go.
 

rrdonutz

Well-known Member
Joined
Jan 15, 2003
Messages
564

ADVERTISEMENT

Originally posted by 2rrs:Would this do?

="Quarter"&" " &INT((MONTH(E7)+2)/3)
Hi 2rrs,

It will work if E7 (or D7) is already a serial number, custom formatted as "mmm". Otherwise, this variation of your formula will work if it is TEXT only (with no underlying serial number):

="Quarter "&INT((MONTH(D7&1)+2)/3)

--Tom
 

Ima_learnin'

Active Member
Joined
Dec 14, 2002
Messages
266
Thanks Everyone!

It's Text only...so Tom's formula worked perfect!

thanks again, Ima Learnin'
 

2rrs

Active Member
Joined
Dec 25, 2002
Messages
346

ADVERTISEMENT

rrdonutz said:


Hi 2rrs,

It will work if E7 (or D7) is already a serial number, custom formatted as "mmm". Otherwise, this variation of your formula will work if it is TEXT only (with no underlying serial number):

="Quarter "&INT((MONTH(D7&1)+2)/3)

--Tom


Hi Tom,
Thanks for your comments. I'm not sure I understand.
I tested successfully against dates formatted as serial numbers using 3/4/97 format, and checked against several other standard date formats. I did not need to custom format as "mmm".
I like the method you use to add a space betwn "Quarter and 1" without the need for the extra &" ".
Appreciate your input, 2rrs
 

just_jon

Legend
Joined
Sep 3, 2002
Messages
10,473
I think Tom was meaning that if D7 held a literal Text FEB that your original post would error, as MONTH(FEB) will not reduce to a value while the modified MONTH(FEB1) will.
 

rrdonutz

Well-known Member
Joined
Jan 15, 2003
Messages
564
Originally posted by just_jon:
I think Tom was meaning that if D7 held a literal Text FEB that your original post would error, as MONTH(FEB) will not reduce to a value while the modified MONTH(FEB1) will.
Yes, that is what I meant. Glad someone's around to get my meaning across. And I can't think of anyone better than you, Jon. Congratulations on the new designation! Well deserved, and it certainly makes your avatar more appropriate now (i.e., the big head)!
 

just_jon

Legend
Joined
Sep 3, 2002
Messages
10,473
rrdonutz said:
Originally posted by just_jon:
I think Tom was meaning that if D7 held a literal Text FEB that your original post would error, as MONTH(FEB) will not reduce to a value while the modified MONTH(FEB1) will.
Yes, that is what I meant. Glad someone's around to get my meaning across. And I can't think of anyone better than you, Jon. Congratulations on the new designation! Well deserved, and it certainly makes your avatar more appropriate now (i.e., the big head)!

Thanks, Tom -- means a lot from you. [Try to get my head back to size soon... LOWER(HEAD), maybe?] :LOL:
 
Learn Excel from Bill Jelen

Understanding data is crucial, and the easiest place to start is with Microsoft Excel.

Forum statistics

Threads
1,151,561
Messages
5,765,109
Members
425,260
Latest member
worldbfreebase

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top