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

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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)!
 
Upvote 0
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:
 
Upvote 0

Forum statistics

Threads
1,213,552
Messages
6,114,278
Members
448,560
Latest member
Torchwood72

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
Back
Top