Avoiding ifs within ifs

TheNewGuy22

New Member
Joined
Mar 19, 2014
Messages
38
Hello Everybody again,

I'm having difficulty figuring out how to set up an if statement without putting 12 ifs within each other.

I'm trying to get a formula that recognizes what month it is and then displays either FYyy or yyyy based on the month it shows and the date.

This is what I have. I want a way to get the bolded result based on the columns already there.

NovFY0911/5/200812/5/20082008
DecFY0912/6/20081/6/20092008
JanFY091/7/20092/4/20092009
FebFY092/5/20093/5/20092009
MarFY093/6/20094/3/20092009
AprFY094/4/20095/5/2009FY09
MayFY095/6/20096/3/2009FY09
JunFY096/4/20097/6/2009FY09
JulFY107/7/20098/5/2009FY10
AugFY108/6/20099/3/2009FY10
SepFY109/4/200910/5/2009FY10
OctFY1010/6/200911/3/20092009

<tbody>
</tbody>

Is there a way to list off say:
{Jan,Feb,Mar,Apr,May,Jun,Jul,Aug,Sep,Oct,Nov,Dec} and then have an equivalent array with the possible outcomes?

Thank you very much,
Robin
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
Is there a way to list off say:
{Jan,Feb,Mar,Apr,May,Jun,Jul,Aug,Sep,Oct,Nov,Dec} and then have an equivalent array with the possible outcomes?

Potentially, yes with the LOOKUP formula, but I think we need more information.
What exactly is the critiera for determining the bolded column? What determines whether to use FYyy or yyyy? Which date column are you using for yyyy (column 3 or column 4)?
 
Upvote 0
FYyy is meant to be any month between Apr and Sep. Oct to Mar will be yyyy. The columns are start date and end date. I'm using the month that correlates most to that time frame. I'd have yyyy calculated in the same manner.

So that is why 12/6/2008 - 1/6/2009 is set to 2008 and not 2009.
 
Upvote 0
FYyy is meant to be any month between Apr and Sep. Oct to Mar will be yyyy. The columns are start date and end date. I'm using the month that correlates most to that time frame. I'd have yyyy calculated in the same manner.

So that is why 12/6/2008 - 1/6/2009 is set to 2008 and not 2009.

Which month is being used to determine that... the one shown in Column A or the one shown in Column C?
 
Upvote 0
Column A.

Column A is already using the following equation:

Code:
=IFERROR(TEXT(DATE(2000,MODE(MONTH(C2+(ROW(INDIRECT("1:"&1+D2-C2))-1))),1),"mmm"),"")

This simply states which month has more dates within the range.

Column B uses the following equation:
Code:
=IF(C2=0,"","FY"&RIGHT(YEAR(C2)-(MONTH(C2)<=6)+1,2))

Column C and D are inputted. I want an equation for Column E that reflects these columns that have already been calculated.
 
Upvote 0
OK, Here I go...

First, may I recommend this much simpler formula for column A
=TEXT(C2+((D2-C2)/2),"MMM")

Secondly, try this for column E using the same concept
=IF(AND(MONTH(C2+((D2-C2)/2))>=4,MONTH(C2+((D2-C2)/2))<=9),B2,TEXT(C2+((D2-C2)/2),"YYYY"))
 
Upvote 0
Column A.

Column A is already using the following equation:

Code:
=IFERROR(TEXT(DATE(2000,MODE(MONTH(C2+(ROW(INDIRECT("1:"&1+D2-C2))-1))),1),"mmm"),"")

This simply states which month has more dates within the range.

Column B uses the following equation:
Code:
=IF(C2=0,"","FY"&RIGHT(YEAR(C2)-(MONTH(C2)<=6)+1,2))

Column C and D are inputted. I want an equation for Column E that reflects these columns that have already been calculated.
What would columns A, B and E contain if the date range was, say, 11/29/2008 and 12/29/2008?
 
Upvote 0
Thank you BiocideJ! That worked exactly how I wanted it too. The month equation works, but February seems to give it issues due to the days difference. Is there a way to make sure it doesn't skip February like it did in the following example?

JulFY097/16/20088/14/2008FY09
AugFY098/15/20089/15/2008FY09
SepFY099/16/200810/14/2008FY09
OctFY0910/15/200811/14/20082008
NovFY0911/15/200812/16/20082008
DecFY0912/17/20081/15/20092008
JanFY091/16/20092/13/20092009
MarFY092/14/20093/16/20092009
MarFY093/17/20094/15/20092009
AprFY094/16/20095/14/2009FY09
MayFY095/15/20096/12/2009FY09
JunFY096/13/20097/15/2009FY09

<colgroup><col><col><col><col><col></colgroup><tbody>
</tbody>
 
Upvote 0
I would still be interested in knowing the answer to the question I asked in Message #7.
 
Upvote 0
The month equation works, but February seems to give it issues due to the days difference. Is there a way to make sure it doesn't skip February like it did in the following example?

Can you verify that your examples are not working? I am getting the same results with my formula and your old formula.
Just to test, I manually typed out all of the days between 2/14/2009 and 3/16/2009 (inclusively) and there are 15Feb and 16Mar which, if I understand your convention correctly, should show as MAR.
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,148
Members
448,552
Latest member
WORKINGWITHNOLEADER

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