Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Nested IF Statements, Months of Year
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(MO NTH(A10)=3,"MAR",IF(MONTH(A10)=4,"APR",IF(MONTH(A1 0)=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. -- Wendy |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Nested IF Statements, Months of Year
Try this:
=TEXT(A10,"mmm") -- Biff Microsoft Excel MVP "Wendy" wrote in message ... 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(MO NTH(A10)=3,"MAR",IF(MONTH(A10)=4,"APR",IF(MONTH(A1 0)=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. -- Wendy |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Nested IF Statements, Months of Year
Oh, I am a happy woman today! I had hoped there was a simpler way.
Thank you, thank you, thank you!!! :) -- Wendy "T. Valko" wrote: Try this: =TEXT(A10,"mmm") -- Biff Microsoft Excel MVP "Wendy" wrote in message ... 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(MO NTH(A10)=3,"MAR",IF(MONTH(A10)=4,"APR",IF(MONTH(A1 0)=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. -- Wendy |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Nested IF Statements, Months of Year
Oh, I am a happy woman today!
You should be happy every day! You're welcome. Thanks for the feedback! -- Biff Microsoft Excel MVP "Wendy" wrote in message ... Oh, I am a happy woman today! I had hoped there was a simpler way. Thank you, thank you, thank you!!! :) -- Wendy "T. Valko" wrote: Try this: =TEXT(A10,"mmm") -- Biff Microsoft Excel MVP "Wendy" wrote in message ... 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(MO NTH(A10)=3,"MAR",IF(MONTH(A10)=4,"APR",IF(MONTH(A1 0)=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. -- Wendy |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Nested IF Statements, Months of Year
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 "Wendy" wrote in message ... 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(MO NTH(A10)=3,"MAR",IF(MONTH(A10)=4,"APR",IF(MONTH(A1 0)=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. -- Wendy |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
sumif for months and year | Excel Worksheet Functions | |||
Months are being duplicated in a conditional if statements | Excel Worksheet Functions | |||
Year-Days-Months | Excel Worksheet Functions | |||
Within 6 months, Within 1 year and so on... | Excel Discussion (Misc queries) | |||
How do i change 15 months to read 1 year and 3 months? | Excel Discussion (Misc queries) |