# Need a Formula to evaluate for Fiscal Quarter

#### Ima_learnin'

##### Active Member
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.

#### al_b_cnu

##### Well-known Member
Hi,

could you not use a simple VLOOKUP?

HTH

Alan

#### rrdonutz

##### Well-known Member
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.

#### 2rrs

##### Active Member
Would this do?

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

#### rrdonutz

##### Well-known Member

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
Thanks Everyone!

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

thanks again, Ima Learnin'

#### 2rrs

##### Active Member

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,
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 &" ".

#### just_jon

##### Legend
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
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
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?]

Replies
10
Views
317
Replies
0
Views
130
Replies
11
Views
344
Replies
5
Views
176
Replies
0
Views
267

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

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.

### Which adblocker are you using?

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

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