| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula for selecting a Leap Year.
Hello,
The first formula below checks to see if the word February is in one cell and the year 2012 in another and if so, will place the number 29 in another cell for me and if not, 28. =IF(AND('Monthly Totals'!L8="February",'Monthly Totals'!O8=2012),29,28) I am looking to create a formula that will look for the word February in one cell and one these following years. Not just 2012. 2012, 2016, 2020, 2024, 2028 If February has been selected from a drop window and as well, one of those years from another drop window, I wish the program to know that someone has selected a leap year and will place the date number 29 in a cell. Otherwise, the number 28. This formula works well but it only checks for the year 2012. =IF(AND('Monthly Totals'!L8="February",'Monthly Totals'!O8=2012),29,28) This formula does not work. =IF(AND('Monthly Totals'!L8="February",'Monthly Totals'!O8={2012,2016,2020,2024,2028}),29,28) Thank you in advance for any help you may have to offer. -Bad |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Formula for selecting a Leap Year.
Hello and Thank you for replying to my question.
I would like to use this code; =IF(AND('Monthly Totals'!L8="February",'Monthly Totals'!O8=2012),29,28) with the exception that is looks up these years 2012, 2016, 2020, 2024, 2028 I am using 31 sheets (1 sheet per day), and the code you provided will place 30 or 31 on sheet 29 if any other month is selected. -Bad "Ron Coderre" wrote: With A2: (a month name....eg February) B2: (a year........eg 2012) This formula returns the last day of that month: C2: =MAX(DAY(DATEVALUE("28-"&A2&"-"&B2)+{0,1,2,3})) or...slightly shorter C2: =MAX(DAY(("28-"&A2&"-"&B2)+{0,1,2,3})) In the above examples, the formulas return: 29 Is that something you can work with? Regards, Ron Coderre Microsoft MVP (Excel) "BadBoy" wrote in message ... Hello, The first formula below checks to see if the word February is in one cell and the year 2012 in another and if so, will place the number 29 in another cell for me and if not, 28. =IF(AND('Monthly Totals'!L8="February",'Monthly Totals'!O8=2012),29,28) I am looking to create a formula that will look for the word February in one cell and one these following years. Not just 2012. 2012, 2016, 2020, 2024, 2028 If February has been selected from a drop window and as well, one of those years from another drop window, I wish the program to know that someone has selected a leap year and will place the date number 29 in a cell. Otherwise, the number 28. This formula works well but it only checks for the year 2012. =IF(AND('Monthly Totals'!L8="February",'Monthly Totals'!O8=2012),29,28) This formula does not work. =IF(AND('Monthly Totals'!L8="February",'Monthly Totals'!O8={2012,2016,2020,2024,2028}),29,28) Thank you in advance for any help you may have to offer. -Bad |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Formula for selecting a Leap Year.
Thank you Glen,
This code is just about perfect. All I need now is to have the day stay at 29 if January or any other month other than Feb is selected. =IF('Monthly Totals'!L8="February",DAY(DATE('Monthly Totals'!O8,3,0))) -Bad "Glenn" wrote: BadBoy wrote: Hello, The first formula below checks to see if the word February is in one cell and the year 2012 in another and if so, will place the number 29 in another cell for me and if not, 28. =IF(AND('Monthly Totals'!L8="February",'Monthly Totals'!O8=2012),29,28) I am looking to create a formula that will look for the word February in one cell and one these following years. Not just 2012. 2012, 2016, 2020, 2024, 2028 If February has been selected from a drop window and as well, one of those years from another drop window, I wish the program to know that someone has selected a leap year and will place the date number 29 in a cell. Otherwise, the number 28. This formula works well but it only checks for the year 2012. =IF(AND('Monthly Totals'!L8="February",'Monthly Totals'!O8=2012),29,28) This formula does not work. =IF(AND('Monthly Totals'!L8="February",'Monthly Totals'!O8={2012,2016,2020,2024,2028}),29,28) Thank you in advance for any help you may have to offer. -Bad =IF('Monthly Totals'!L8="February",DAY(DATE('Monthly Totals'!O8,3,0))) |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Formula for selecting a Leap Year.
WEEEEEEEEEEEEEEEEEEEEEEE!!!! Fantabulas!!
Thank you, Thank you, both of you. -Bad "Glenn" wrote: Glenn wrote: BadBoy wrote: Hello, The first formula below checks to see if the word February is in one cell and the year 2012 in another and if so, will place the number 29 in another cell for me and if not, 28. =IF(AND('Monthly Totals'!L8="February",'Monthly Totals'!O8=2012),29,28) I am looking to create a formula that will look for the word February in one cell and one these following years. Not just 2012. 2012, 2016, 2020, 2024, 2028 If February has been selected from a drop window and as well, one of those years from another drop window, I wish the program to know that someone has selected a leap year and will place the date number 29 in a cell. Otherwise, the number 28. This formula works well but it only checks for the year 2012. =IF(AND('Monthly Totals'!L8="February",'Monthly Totals'!O8=2012),29,28) This formula does not work. =IF(AND('Monthly Totals'!L8="February",'Monthly Totals'!O8={2012,2016,2020,2024,2028}),29,28) Thank you in advance for any help you may have to offer. -Bad =IF('Monthly Totals'!L8="February",DAY(DATE('Monthly Totals'!O8,3,0))) Forgot the "false" portion of the formula...not sure what you want the =IF('Monthly Totals'!L8="February",DAY(DATE('Monthly Totals'!O8,3,0)),"Not February") |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Formula for selecting a Leap Year.
BadBoy wrote:
Hello, The first formula below checks to see if the word February is in one cell and the year 2012 in another and if so, will place the number 29 in another cell for me and if not, 28. =IF(AND('Monthly Totals'!L8="February",'Monthly Totals'!O8=2012),29,28) I am looking to create a formula that will look for the word February in one cell and one these following years. Not just 2012. 2012, 2016, 2020, 2024, 2028 If February has been selected from a drop window and as well, one of those years from another drop window, I wish the program to know that someone has selected a leap year and will place the date number 29 in a cell. Otherwise, the number 28. This formula works well but it only checks for the year 2012. =IF(AND('Monthly Totals'!L8="February",'Monthly Totals'!O8=2012),29,28) This formula does not work. =IF(AND('Monthly Totals'!L8="February",'Monthly Totals'!O8={2012,2016,2020,2024,2028}),29,28) Thank you in advance for any help you may have to offer. -Bad =IF('Monthly Totals'!L8="February",DAY(DATE('Monthly Totals'!O8,3,0))) |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Formula for selecting a Leap Year.
The sheets are named 1 through 31.
If February is selected as the month, I have this formula on the 30th and 31st. {=IF('Monthly Totals'!L8="February",0,30)} As for the 31st on a month other than February, I haddn't thought about that. (ha ha) Suggestions? Thank you -Bad "Glenn" wrote: If your sheets are named 1, 2, 3, 4, etc. then the following will work on all sheets: =IF(DAY(DATE('Monthly Total'!O8,MONTH(DATEVALUE("1-"&'Monthly Total'!L8& "-2000"))+1,0))=--MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,99), --MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,99),"") Glenn wrote: How are your daily sheets named? How do you deal with 30 in February and 31 in February, April, June, September and November? BadBoy wrote: Thank you Glen, This code is just about perfect. All I need now is to have the day stay at 29 if January or any other month other than Feb is selected. =IF('Monthly Totals'!L8="February",DAY(DATE('Monthly Totals'!O8,3,0))) -Bad "Glenn" wrote: BadBoy wrote: Hello, The first formula below checks to see if the word February is in one cell and the year 2012 in another and if so, will place the number 29 in another cell for me and if not, 28. =IF(AND('Monthly Totals'!L8="February",'Monthly Totals'!O8=2012),29,28) I am looking to create a formula that will look for the word February in one cell and one these following years. Not just 2012. 2012, 2016, 2020, 2024, 2028 If February has been selected from a drop window and as well, one of those years from another drop window, I wish the program to know that someone has selected a leap year and will place the date number 29 in a cell. Otherwise, the number 28. This formula works well but it only checks for the year 2012. =IF(AND('Monthly Totals'!L8="February",'Monthly Totals'!O8=2012),29,28) This formula does not work. =IF(AND('Monthly Totals'!L8="February",'Monthly Totals'!O8={2012,2016,2020,2024,2028}),29,28) Thank you in advance for any help you may have to offer. -Bad =IF('Monthly Totals'!L8="February",DAY(DATE('Monthly Totals'!O8,3,0))) |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Formula for selecting a Leap Year.
Glenn wrote:
BadBoy wrote: Hello, The first formula below checks to see if the word February is in one cell and the year 2012 in another and if so, will place the number 29 in another cell for me and if not, 28. =IF(AND('Monthly Totals'!L8="February",'Monthly Totals'!O8=2012),29,28) I am looking to create a formula that will look for the word February in one cell and one these following years. Not just 2012. 2012, 2016, 2020, 2024, 2028 If February has been selected from a drop window and as well, one of those years from another drop window, I wish the program to know that someone has selected a leap year and will place the date number 29 in a cell. Otherwise, the number 28. This formula works well but it only checks for the year 2012. =IF(AND('Monthly Totals'!L8="February",'Monthly Totals'!O8=2012),29,28) This formula does not work. =IF(AND('Monthly Totals'!L8="February",'Monthly Totals'!O8={2012,2016,2020,2024,2028}),29,28) Thank you in advance for any help you may have to offer. -Bad =IF('Monthly Totals'!L8="February",DAY(DATE('Monthly Totals'!O8,3,0))) Forgot the "false" portion of the formula...not sure what you want the =IF('Monthly Totals'!L8="February",DAY(DATE('Monthly Totals'!O8,3,0)),"Not February") |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Formula for selecting a Leap Year.
How are your daily sheets named?
How do you deal with 30 in February and 31 in February, April, June, September and November? BadBoy wrote: Thank you Glen, This code is just about perfect. All I need now is to have the day stay at 29 if January or any other month other than Feb is selected. =IF('Monthly Totals'!L8="February",DAY(DATE('Monthly Totals'!O8,3,0))) -Bad "Glenn" wrote: BadBoy wrote: Hello, The first formula below checks to see if the word February is in one cell and the year 2012 in another and if so, will place the number 29 in another cell for me and if not, 28. =IF(AND('Monthly Totals'!L8="February",'Monthly Totals'!O8=2012),29,28) I am looking to create a formula that will look for the word February in one cell and one these following years. Not just 2012. 2012, 2016, 2020, 2024, 2028 If February has been selected from a drop window and as well, one of those years from another drop window, I wish the program to know that someone has selected a leap year and will place the date number 29 in a cell. Otherwise, the number 28. This formula works well but it only checks for the year 2012. =IF(AND('Monthly Totals'!L8="February",'Monthly Totals'!O8=2012),29,28) This formula does not work. =IF(AND('Monthly Totals'!L8="February",'Monthly Totals'!O8={2012,2016,2020,2024,2028}),29,28) Thank you in advance for any help you may have to offer. -Bad =IF('Monthly Totals'!L8="February",DAY(DATE('Monthly Totals'!O8,3,0))) |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Formula for selecting a Leap Year.
If your sheets are named 1, 2, 3, 4, etc. then the following will work on all
sheets: =IF(DAY(DATE('Monthly Total'!O8,MONTH(DATEVALUE("1-"&'Monthly Total'!L8& "-2000"))+1,0))=--MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,99), --MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,99),"") Glenn wrote: How are your daily sheets named? How do you deal with 30 in February and 31 in February, April, June, September and November? BadBoy wrote: Thank you Glen, This code is just about perfect. All I need now is to have the day stay at 29 if January or any other month other than Feb is selected. =IF('Monthly Totals'!L8="February",DAY(DATE('Monthly Totals'!O8,3,0))) -Bad "Glenn" wrote: BadBoy wrote: Hello, The first formula below checks to see if the word February is in one cell and the year 2012 in another and if so, will place the number 29 in another cell for me and if not, 28. =IF(AND('Monthly Totals'!L8="February",'Monthly Totals'!O8=2012),29,28) I am looking to create a formula that will look for the word February in one cell and one these following years. Not just 2012. 2012, 2016, 2020, 2024, 2028 If February has been selected from a drop window and as well, one of those years from another drop window, I wish the program to know that someone has selected a leap year and will place the date number 29 in a cell. Otherwise, the number 28. This formula works well but it only checks for the year 2012. =IF(AND('Monthly Totals'!L8="February",'Monthly Totals'!O8=2012),29,28) This formula does not work. =IF(AND('Monthly Totals'!L8="February",'Monthly Totals'!O8={2012,2016,2020,2024,2028}),29,28) Thank you in advance for any help you may have to offer. -Bad =IF('Monthly Totals'!L8="February",DAY(DATE('Monthly Totals'!O8,3,0))) |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Formula for selecting a Leap Year.
Suggestion below.
BadBoy wrote: The sheets are named 1 through 31. If February is selected as the month, I have this formula on the 30th and 31st. {=IF('Monthly Totals'!L8="February",0,30)} As for the 31st on a month other than February, I haddn't thought about that. (ha ha) Suggestions? Thank you -Bad "Glenn" wrote: If your sheets are named 1, 2, 3, 4, etc. then the following will work on all sheets: =IF(DAY(DATE('Monthly Total'!O8,MONTH(DATEVALUE("1-"&'Monthly Total'!L8& "-2000"))+1,0))=--MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,99), --MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,99),"") Glenn wrote: How are your daily sheets named? How do you deal with 30 in February and 31 in February, April, June, September and November? BadBoy wrote: Thank you Glen, This code is just about perfect. All I need now is to have the day stay at 29 if January or any other month other than Feb is selected. =IF('Monthly Totals'!L8="February",DAY(DATE('Monthly Totals'!O8,3,0))) -Bad "Glenn" wrote: BadBoy wrote: Hello, The first formula below checks to see if the word February is in one cell and the year 2012 in another and if so, will place the number 29 in another cell for me and if not, 28. =IF(AND('Monthly Totals'!L8="February",'Monthly Totals'!O8=2012),29,28) I am looking to create a formula that will look for the word February in one cell and one these following years. Not just 2012. 2012, 2016, 2020, 2024, 2028 If February has been selected from a drop window and as well, one of those years from another drop window, I wish the program to know that someone has selected a leap year and will place the date number 29 in a cell. Otherwise, the number 28. This formula works well but it only checks for the year 2012. =IF(AND('Monthly Totals'!L8="February",'Monthly Totals'!O8=2012),29,28) This formula does not work. =IF(AND('Monthly Totals'!L8="February",'Monthly Totals'!O8={2012,2016,2020,2024,2028}),29,28) Thank you in advance for any help you may have to offer. -Bad =IF('Monthly Totals'!L8="February",DAY(DATE('Monthly Totals'!O8,3,0))) |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| IF formula to allow Feb 29th in a leap year | Excel Discussion (Misc queries) | |||
| How do I change the following formula to allow for leap year | Excel Discussion (Misc queries) | |||
| leap year | Excel Discussion (Misc queries) | |||
| Leap Year | Charts and Charting in Excel | |||
| How to determine if year is a leap year | Excel Worksheet Functions | |||
Linear Mode
