Nested IF Statements, Months of Year

[Wendy]

Hi all,

Not sure if I'm not finding the function I want elsewhere, or trying to
over-complicate the situation, but this is what I need:

I have an spreadsheet of course registrations, for different course dates
(in the same calendar year - ie 2008), and I want to be able to find all
courses for a single month. Each registration (including course date is a
single row).

This information would be found by entering into a single cell, the month
(OCT). The information in this cell would then be acted upon by an Advanced
Filter via a macro button to find all results, and paste a copy of the
results elsewhere on the spreadsheet for further action. (Adv filter and
macro button procedure works elsewhere, no issues on that section of
procedure.)

My problem is that I don't know how to best get the month information so
that it can be recognised by the single cell to determine which month (range
of data) it is searching for.

I can add an additional column, and have managed to successfully get a
nested IF statement to work for several months, but then of course run into
the 7-nested IF statements rule, which gives me a problem for the second half
of the year....

Where
A2 = Cell to specify course month to search for data (for adv filter)
A10 = Course Date, listed in date format.
B10 = Course Month (with format below, used as the reference field for the
parameter given in cell A2

eg
=IF(MONTH(A10)=1,"JAN",IF(MONTH(A10)=2,"FEB",IF(MONTH(A10)=3,"MAR",IF(MONTH(A10)=4,"APR",IF(MONTH(A10)=5,"MAY",IF(MONTH(A10)=6,"JUN",""))))))

I tried to do a LOOKUP statement using the Month statement, but it didn't
like it, or I can't seem to get it right.

I can get a month number to display in a column if you enter just a month in
that date, but as far as I can see, this is just a display, not something I
can enact upon in an advanced filter (eg)

Enter 1 Sept or 23 Sept, it displays as Sept 08 (date format display), but
it is still the date entered particularly, and doesn't encompass all dates
that month.

All help gratefully received, thanks.

 

[T. Valko]

Try this:

=TEXT(A10,"mmm")

 

[Wendy]

Oh, I am a happy woman today! I had hoped there was a simpler way.

Thank you, thank you, thank you!!!

 

[T. Valko]

Oh, I am a happy woman today!

You should be happy every day!

You're welcome. Thanks for the feedback!

 

[Arvi Laanemets]

Hi

I myself prefer to use 1st dates of months.

P.e. On separate sheet p.e. Months, into cell A1 you enter year, and define
it as named range Year;
Into cell A2 you enter the formula '=DATE(Year,ROW()-1,1)' and copy it to
range A2:A13. Format the range p.e. as "mmm", and define it as named range
Months;
In your table, for Month column apply data validation list with '=Months' as
source, and format the column as "mmm".

Now you can simply select months from drop-down lists, or you can enter them
as dates (1st of month, other dates aren´t allowed). In all calculations and
references you use Month column data as dates. It allows you use comparision
operators (>, <, etc.) to select a range of months when needed, and to sort
your table by month.

In next year you take a copy of workbook, clear all entered data, and on
Months sheet, you enter new year number.

In case you have exact course (start) date in another column anyway, it will
be even simpler - you use DATE function to calculate the 1st of month given
by date, like
=IF(A1="","",DATE(YEAR(A1),MONTH(A1),1))
, and format the colum as "mmm"


Arvi Laanemets