Avoiding ifs within ifs

[TheNewGuy22]

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

<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

 

[TheNewGuy22]

@Rick Rothstein

Column A	Column B	Column C	Column D	Column E
Dec	FY09	11/29/2008	12/29/2008	2008

<colgroup><col><col><col><col><col></colgroup><tbody>
</tbody>

Those are the entries I'd want from your dates.


@BiocideJ

I've tried both equations several times and I'm getting the same results.

I've modified the equation slightly to not show anything if the date field is empty.

=IF((C11+((D11-C11)/2)=0),"",TEXT(C11+((D11-C11)/2),"MMM"))

Column A	Column B	Column C	Column D	Column E
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

<colgroup><col><col><col><col><col></colgroup><tbody>
</tbody>

I'd like the first Mar labeled to show up as Feb.

 

[BiocideJ]

What is the reasoning that this should show as February? I'm not questioning whether it should or shouldn't as that is up to you. I just need to understand the convention that determines which month name to display so I can adjust the formula accordingly. Initially you had mentioned that it was the month that had more days in the date range, but perhaps there is more to it?

 

[platonic567]

Column A	Column B	Column C	Column D	Column E
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

<TBODY>
</TBODY>

I'd like the first Mar labeled to show up as Feb.

Unless I am wrong, Column A appears to be simply month in 3 characters text format of Column C

So why not simply the below formula for 2/14/2009 to show as Feb:

=TEXT(C2,"mmm")

 

[BiocideJ]

Unless I am wrong, Column A appears to be simply month in 3 characters text format of Column C

So why not simply the below formula for 2/14/2009 to show as Feb:

=TEXT(C2,"mmm")

If only, but see his first example in the above post

Column A	Column B	Column C	Column D	Column E
Dec	FY09	11/29/2008	12/29/2008	2008

<TBODY>
</TBODY>

 

[TheNewGuy22]

I have many data points that have a wide range of dates that could start at any day of the month. These start and end dates could last anywhere from 25-35 days. There are 12 entries a year, and it makes it clean to label these entries to the month that it corresponds to.

The start dates that start on say January 1st are easy to see. They would be linked to January. However a data point that has a start date on January 31st to March 1st creates complications. I can't link it using the starting date cell. I have to use both the start and end date cells. The code you gave me managed to accurately give me the month it would correspond to if that scenario occurs.

The next scenario that creates issues is from the sample posted above. With the start and end date in the middle of a month there is no obvious month. I think this is best suited to have the first cell in this table register a month and then the cells under it go to the next month while confirming that it is a valid month within the date range.

 

[BiocideJ]

Well, that is exactly what that formula does. However, in the example you provide, 2/14/2009 to 3/16/2009 there are 15 days in February 14-28 and 16 days in March 1-16. This makes March have one extra day and is why it shows as Mar.

For the scenario you describe, the date range 2/14/2009 to 3/15/2009 would have 15 days in both Feb and Mar and the output will show as Feb as you requested.
Let me know if I am totally missing something.

ADDITION:
Also, 2/14/2008 to 3/16/2008 = FEB because 2008 was a leap year and there are 16 days in FEB that year.

 

[TheNewGuy22]

Sadly I can't change the dates of the months. These months are more of titles that are used to calculate means and other statistical data. By having a situation in which 2 months appear within the same year it will throw off the data. It is relatively the same dates from one year to the next.

My thought on how to approach this is to do as followed:

Recognize if a month is in the cell above.

If there isn't a month above it will calculate the month from our original equation. (This is for row #1)

If there is a month above it, it will take that month and go to the next month. (For example cell above is Jan, so it will want to set the cell to Feb)
Afterwards, it will register all the months that have more then 12 days active in the start and end date range. (Say the range is 2/14/2009 to 3/16/2009. This will see Feb and Mar)

If the start and end date has the month as an available option it will set it to that month. (And(Feb, Or(Feb, Mar)), Feb)



I'm slightly grasping how to do this, but I'm not too sure on how to handle the recognition of months within the range.

 

[BiocideJ]

Like this?

=IF(A10="",IF((C11+((D11-C11)/2)=0),"",TEXT(C11+((D11-C11)/2),"MMM")),TEXT(EOMONTH(DATEVALUE(A10&"1900"),1),"MMM"))

It is a bit convoluted, but I believe it will do what you just said.

Although honestly, I am confused by this:

Afterwards, it will register all the months that have more then 12 days active in the start and end date range. (Say the range is 2/14/2009 to 3/16/2009. This will see Feb and Mar)

If the start and end date has the month as an available option it will set it to that month. (And(Feb, Or(Feb, Mar)), Feb)

 

[platonic567]

Or just put -1 in BiocideJ's formula

Below give Feb for 2-14-2009 - 3-16-2009 :

=TEXT(C2+((D2-C2)/2-1),"MMM")

 

[TheNewGuy22]

@platonic567

As much as I wish I could use that, I have over 16,000 data points... I rather not be spending all my time changing it manually for the occasional error.

@BiocideJ

So I got it working thanks to your formula. I appreciate your help. I need to get a better grasp to be able to think and create these formulas a little faster.

Thanks again. You were very helpful.