Thanks:  0
Likes:  0

1. Avoiding ifs within ifs

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.

 Nov FY09 11/5/2008 12/5/2008 2008 Dec FY09 12/6/2008 1/6/2009 2008 Jan FY09 1/7/2009 2/4/2009 2009 Feb FY09 2/5/2009 3/5/2009 2009 Mar FY09 3/6/2009 4/3/2009 2009 Apr FY09 4/4/2009 5/5/2009 FY09 May FY09 5/6/2009 6/3/2009 FY09 Jun FY09 6/4/2009 7/6/2009 FY09 Jul FY10 7/7/2009 8/5/2009 FY10 Aug FY10 8/6/2009 9/3/2009 FY10 Sep FY10 9/4/2009 10/5/2009 FY10 Oct FY10 10/6/2009 11/3/2009 2009

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

2. Re: Avoiding ifs within ifs

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)?

3. Re: Avoiding ifs within ifs

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.

4. Re: Avoiding ifs within ifs

Originally Posted by TheNewGuy22
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?

5. Re: Avoiding ifs within ifs

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.

6. Re: Avoiding ifs within ifs

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"))

7. Re: Avoiding ifs within ifs

Originally Posted by TheNewGuy22
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?

8. Re: Avoiding ifs within ifs

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?

 Jul FY09 7/16/2008 8/14/2008 FY09 Aug FY09 8/15/2008 9/15/2008 FY09 Sep FY09 9/16/2008 10/14/2008 FY09 Oct FY09 10/15/2008 11/14/2008 2008 Nov FY09 11/15/2008 12/16/2008 2008 Dec FY09 12/17/2008 1/15/2009 2008 Jan FY09 1/16/2009 2/13/2009 2009 Mar FY09 2/14/2009 3/16/2009 2009 Mar FY09 3/17/2009 4/15/2009 2009 Apr FY09 4/16/2009 5/14/2009 FY09 May FY09 5/15/2009 6/12/2009 FY09 Jun FY09 6/13/2009 7/15/2009 FY09

9. Re: Avoiding ifs within ifs

I would still be interested in knowing the answer to the question I asked in Message #7.

10. Re: Avoiding ifs within ifs

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.

User Tag List

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•