Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
leap year problem.
hello,
I have this formula : =IF(OR(MOD(B1,400)=0,AND(MOD(B1,4)=0,MOD(B1,100)< 0)),"L","NL" (to show L if the date falls in a leap year). It works for 2008 only not for any other leap year. please help! thanks. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
leap year problem.
Your formula appears to work for me... which years do you think it doesn't
work for? In case you are interested, here is a shorter formula to do the same thing... =IF(MONTH(DATE(B1,2,29))=2,"L","NL") -- Rick (MVP - Excel) "2vao" wrote in message ... hello, I have this formula : =IF(OR(MOD(B1,400)=0,AND(MOD(B1,4)=0,MOD(B1,100)< 0)),"L","NL" (to show L if the date falls in a leap year). It works for 2008 only not for any other leap year. please help! thanks. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
leap year problem.
to elaborate on my problem , cell b1 is formatted as mmm-yy so when I input
Jan-08 it shows "L", if input any other year, like Jan-04 or Jan-12 or Jan-16 it shows "NL". "Rick Rothstein" wrote: Your formula appears to work for me... which years do you think it doesn't work for? In case you are interested, here is a shorter formula to do the same thing... =IF(MONTH(DATE(B1,2,29))=2,"L","NL") -- Rick (MVP - Excel) "2vao" wrote in message ... hello, I have this formula : =IF(OR(MOD(B1,400)=0,AND(MOD(B1,4)=0,MOD(B1,100)< 0)),"L","NL" (to show L if the date falls in a leap year). It works for 2008 only not for any other leap year. please help! thanks. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
leap year problem.
Same principle applies, just clarify you want the YEAR from B1 only.
=IF(MONTH(DATE(YEAR(B1),2,29))=2,"L","NL") -- Please remember to indicate when the post is answered so others can benefit from it later. "2vao" wrote: to elaborate on my problem , cell b1 is formatted as mmm-yy so when I input Jan-08 it shows "L", if input any other year, like Jan-04 or Jan-12 or Jan-16 it shows "NL". "Rick Rothstein" wrote: Your formula appears to work for me... which years do you think it doesn't work for? In case you are interested, here is a shorter formula to do the same thing... =IF(MONTH(DATE(B1,2,29))=2,"L","NL") -- Rick (MVP - Excel) "2vao" wrote in message ... hello, I have this formula : =IF(OR(MOD(B1,400)=0,AND(MOD(B1,4)=0,MOD(B1,100)< 0)),"L","NL" (to show L if the date falls in a leap year). It works for 2008 only not for any other leap year. please help! thanks. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
leap year problem.
Thanks Rick and KC, although both answers helped me - KC's did not require
any format to extract the year. I also discouvered that for =IF(OR(MOD(B1,400)=0,AND(MOD(B1,4)=0,MOD(B1,100)< 0)),"L","NL") to work, it had to be formatted to "YYYY". Many thanks both of you. "KC" wrote: Same principle applies, just clarify you want the YEAR from B1 only. =IF(MONTH(DATE(YEAR(B1),2,29))=2,"L","NL") -- Please remember to indicate when the post is answered so others can benefit from it later. "2vao" wrote: to elaborate on my problem , cell b1 is formatted as mmm-yy so when I input Jan-08 it shows "L", if input any other year, like Jan-04 or Jan-12 or Jan-16 it shows "NL". "Rick Rothstein" wrote: Your formula appears to work for me... which years do you think it doesn't work for? In case you are interested, here is a shorter formula to do the same thing... =IF(MONTH(DATE(B1,2,29))=2,"L","NL") -- Rick (MVP - Excel) "2vao" wrote in message ... hello, I have this formula : =IF(OR(MOD(B1,400)=0,AND(MOD(B1,4)=0,MOD(B1,100)< 0)),"L","NL" (to show L if the date falls in a leap year). It works for 2008 only not for any other leap year. please help! thanks. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
leap year problem.
In the formula you posted, you used B1... you should have used YEAR(B1) in
every location you now have just B1 as the calculation you are doing should be against the year value only, not the entire date. The same would apply to my formula... =IF(MONTH(DATE(YEAR(B1),2,29))=2,"L","NL") -- Rick (MVP - Excel) "2vao" wrote in message ... to elaborate on my problem , cell b1 is formatted as mmm-yy so when I input Jan-08 it shows "L", if input any other year, like Jan-04 or Jan-12 or Jan-16 it shows "NL". "Rick Rothstein" wrote: Your formula appears to work for me... which years do you think it doesn't work for? In case you are interested, here is a shorter formula to do the same thing... =IF(MONTH(DATE(B1,2,29))=2,"L","NL") -- Rick (MVP - Excel) "2vao" wrote in message ... hello, I have this formula : =IF(OR(MOD(B1,400)=0,AND(MOD(B1,4)=0,MOD(B1,100)< 0)),"L","NL" (to show L if the date falls in a leap year). It works for 2008 only not for any other leap year. please help! thanks. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
leap year problem.
I also discouvered that for
=IF(OR(MOD(B1,400)=0,AND(MOD(B1,4)=0,MOD(B1,100)< 0)),"L","NL") to work, it had to be formatted to "YYYY". Which, if you had done that, then my formula would have worked and KC's wouldn't. You have to make the right values available for your formulas to use or, if they can't be made right in the cell being referenced, then you have to manipulate the value for the formula yourself. -- Rick (MVP - Excel) "2vao" wrote in message ... Thanks Rick and KC, although both answers helped me - KC's did not require any format to extract the year. I also discouvered that for =IF(OR(MOD(B1,400)=0,AND(MOD(B1,4)=0,MOD(B1,100)< 0)),"L","NL") to work, it had to be formatted to "YYYY". Many thanks both of you. "KC" wrote: Same principle applies, just clarify you want the YEAR from B1 only. =IF(MONTH(DATE(YEAR(B1),2,29))=2,"L","NL") -- Please remember to indicate when the post is answered so others can benefit from it later. "2vao" wrote: to elaborate on my problem , cell b1 is formatted as mmm-yy so when I input Jan-08 it shows "L", if input any other year, like Jan-04 or Jan-12 or Jan-16 it shows "NL". "Rick Rothstein" wrote: Your formula appears to work for me... which years do you think it doesn't work for? In case you are interested, here is a shorter formula to do the same thing... =IF(MONTH(DATE(B1,2,29))=2,"L","NL") -- Rick (MVP - Excel) "2vao" wrote in message ... hello, I have this formula : =IF(OR(MOD(B1,400)=0,AND(MOD(B1,4)=0,MOD(B1,100)< 0)),"L","NL" (to show L if the date falls in a leap year). It works for 2008 only not for any other leap year. please help! thanks. |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
leap year problem.
"2vao" wrote:
to elaborate on my problem , cell b1 is formatted as mmm-yy so when I input Jan-08 it shows "L", if input any other year, like Jan-04 or Jan-12 or Jan-16 it shows "NL". The __Excel__ format of B1 has nothing to do with it, as long as B1 is not the Text format and you input a date in some form. I presume you are referring to the short date format under Regional Settings in the Control Panel (Win XP). Then yes, your formula (mis)behaves as you describe if the short date form is mmm-yy. But that is only by coincidence. The root cause of the failure of your formula, in general, is that your formula assumes that B1 contains only a year, not a date serial number. Jan 2008 (interpeted as 1 Jan 2008) works only because it is the date serial number 39448 (days since 31 Dec 1899). In contrast, Jan 2012 is the serial number 40909. "2vao" wrote later: I also discouvered that for =IF(OR(MOD(B1,400)=0,AND(MOD(B1,4)=0,MOD(B1,100)< 0)),"L","NL") to work, it had to be formatted to "YYYY". Again, the __Excel__ format does not matter. And here, you cannot be talking about the short date format under Regional Settings, because YYYY is not permitted. (At least, not on my Win XP SP2.) I presume you mean that B1 contains only a year. That is a value, not a format. Thanks Rick and KC [....] - KC's did not require any format to extract the year. Non sequitur! It is not clear to me that you realize that the correct solution depends on the correct specification of what B1 contains, which you were not clear about from the start. GIGO! If B1 contains only a year, then use: =if(month(date(B1,2,29))=2, "L", "NL") If B1 contains a date (in any format), then use: =if(month(date(year(B1),2,29))=2, "L", "NL") ----- original message ----- "2vao" wrote in message ... Thanks Rick and KC, although both answers helped me - KC's did not require any format to extract the year. I also discouvered that for =IF(OR(MOD(B1,400)=0,AND(MOD(B1,4)=0,MOD(B1,100)< 0)),"L","NL") to work, it had to be formatted to "YYYY". Many thanks both of you. "KC" wrote: Same principle applies, just clarify you want the YEAR from B1 only. =IF(MONTH(DATE(YEAR(B1),2,29))=2,"L","NL") -- Please remember to indicate when the post is answered so others can benefit from it later. "2vao" wrote: to elaborate on my problem , cell b1 is formatted as mmm-yy so when I input Jan-08 it shows "L", if input any other year, like Jan-04 or Jan-12 or Jan-16 it shows "NL". "Rick Rothstein" wrote: Your formula appears to work for me... which years do you think it doesn't work for? In case you are interested, here is a shorter formula to do the same thing... =IF(MONTH(DATE(B1,2,29))=2,"L","NL") -- Rick (MVP - Excel) "2vao" wrote in message ... hello, I have this formula : =IF(OR(MOD(B1,400)=0,AND(MOD(B1,4)=0,MOD(B1,100)< 0)),"L","NL" (to show L if the date falls in a leap year). It works for 2008 only not for any other leap year. please help! thanks. |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
leap year problem.
On Thu, 23 Jul 2009 13:01:04 -0700, 2vao
wrote: to elaborate on my problem , cell b1 is formatted as mmm-yy so when I input Jan-08 it shows "L", if input any other year, like Jan-04 or Jan-12 or Jan-16 it shows "NL". The date format of an excel cell is completely irrelevant to how Excel interprets your input. Having written that, I don't understand how you are getting an "L" when you type in Jan-08 unless your computer date is set to 2008. Ordinarily, if you type in Jan-08, Excel will interpret that as being 8-Jan-current_year. Same with your other entries. After entering your date, look at the formula bar to see what's really there. --ron |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
leap year problem.
Try this:
=IF(MOD(YEAR(B1),4)=0,"L","NL") "2vao" wrote: to elaborate on my problem , cell b1 is formatted as mmm-yy so when I input Jan-08 it shows "L", if input any other year, like Jan-04 or Jan-12 or Jan-16 it shows "NL". "Rick Rothstein" wrote: Your formula appears to work for me... which years do you think it doesn't work for? In case you are interested, here is a shorter formula to do the same thing... =IF(MONTH(DATE(B1,2,29))=2,"L","NL") -- Rick (MVP - Excel) "2vao" wrote in message ... hello, I have this formula : =IF(OR(MOD(B1,400)=0,AND(MOD(B1,4)=0,MOD(B1,100)< 0)),"L","NL" (to show L if the date falls in a leap year). It works for 2008 only not for any other leap year. please help! thanks. |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
leap year problem.
That formula will work as long as the OP *never* hits a date in the year
2100. -- Rick (MVP - Excel) "Teethless mama" wrote in message ... Try this: =IF(MOD(YEAR(B1),4)=0,"L","NL") "2vao" wrote: to elaborate on my problem , cell b1 is formatted as mmm-yy so when I input Jan-08 it shows "L", if input any other year, like Jan-04 or Jan-12 or Jan-16 it shows "NL". "Rick Rothstein" wrote: Your formula appears to work for me... which years do you think it doesn't work for? In case you are interested, here is a shorter formula to do the same thing... =IF(MONTH(DATE(B1,2,29))=2,"L","NL") -- Rick (MVP - Excel) "2vao" wrote in message ... hello, I have this formula : =IF(OR(MOD(B1,400)=0,AND(MOD(B1,4)=0,MOD(B1,100)< 0)),"L","NL" (to show L if the date falls in a leap year). It works for 2008 only not for any other leap year. please help! thanks. |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
leap year problem.
"Ron Rosenfeld" wrote:
I don't understand how you are getting an "L" when you type in Jan-08 unless your computer date is set to 2008. Ordinarily, if you type in Jan-08, Excel will interpret that as being 8-Jan-current_year. I had the same reaction initially. Then I thought about Regional Settings in the Control Panel (Win XP). As I wrote in an earlier posting in this thread, when the short date format is set to mmm-yy, entering Jan-08 in Excel is interpreted as 1-Jan-2008. So I inferred that it is the short date format that the OP is talking about, not an Excel format. After entering your date, look at the formula bar to see what's really there. In the aforementioned earlier posting, I then explained why the OP's MOD formula failed. ----- original message ---- "Ron Rosenfeld" wrote in message ... On Thu, 23 Jul 2009 13:01:04 -0700, 2vao wrote: to elaborate on my problem , cell b1 is formatted as mmm-yy so when I input Jan-08 it shows "L", if input any other year, like Jan-04 or Jan-12 or Jan-16 it shows "NL". The date format of an excel cell is completely irrelevant to how Excel interprets your input. Having written that, I don't understand how you are getting an "L" when you type in Jan-08 unless your computer date is set to 2008. Ordinarily, if you type in Jan-08, Excel will interpret that as being 8-Jan-current_year. Same with your other entries. After entering your date, look at the formula bar to see what's really there. --ron |
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
leap year problem.
PS....
I wrote: thread, when the short date format is set to mmm-yy, entering Jan-08 in Excel is interpreted as 1-Jan-2008. So I inferred that it is the short date format that the OP is talking about, not an Excel format. Considering the Greg House Rule :), it occurred to me that perhaps the OP is really entering 1/1/2008, 1/1/2012 etc using the default US regional settings, and the OP is indeed relying on Excel formatting to change the appearance. I know, I know: that is not what the OP wrote. But then again, consider the accuracy of the OP's varied postings ;). ----- original message ----- "JoeU2004" wrote in message ... "Ron Rosenfeld" wrote: I don't understand how you are getting an "L" when you type in Jan-08 unless your computer date is set to 2008. Ordinarily, if you type in Jan-08, Excel will interpret that as being 8-Jan-current_year. I had the same reaction initially. Then I thought about Regional Settings in the Control Panel (Win XP). As I wrote in an earlier posting in this thread, when the short date format is set to mmm-yy, entering Jan-08 in Excel is interpreted as 1-Jan-2008. So I inferred that it is the short date format that the OP is talking about, not an Excel format. After entering your date, look at the formula bar to see what's really there. In the aforementioned earlier posting, I then explained why the OP's MOD formula failed. ----- original message ---- "Ron Rosenfeld" wrote in message ... On Thu, 23 Jul 2009 13:01:04 -0700, 2vao wrote: to elaborate on my problem , cell b1 is formatted as mmm-yy so when I input Jan-08 it shows "L", if input any other year, like Jan-04 or Jan-12 or Jan-16 it shows "NL". The date format of an excel cell is completely irrelevant to how Excel interprets your input. Having written that, I don't understand how you are getting an "L" when you type in Jan-08 unless your computer date is set to 2008. Ordinarily, if you type in Jan-08, Excel will interpret that as being 8-Jan-current_year. Same with your other entries. After entering your date, look at the formula bar to see what's really there. --ron |
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
leap year problem.
On Thu, 23 Jul 2009 21:31:27 -0700, "JoeU2004" wrote:
I had the same reaction initially. Then I thought about Regional Settings in the Control Panel (Win XP). As I wrote in an earlier posting in this thread, when the short date format is set to mmm-yy, entering Jan-08 in Excel is interpreted as 1-Jan-2008. So I inferred that it is the short date format that the OP is talking about, not an Excel format. After entering your date, look at the formula bar to see what's really there. In the aforementioned earlier posting, I then explained why the OP's MOD formula failed. I hadn't seen your posting when I wrote mine. And the OP had written that it was the Excel Cell that was formatted as mmm-yy. In any event, I think the general principle of checking the formula bar to see how Excel has interpreted one's input, is a useful approach. --ron |
#15
Posted to microsoft.public.excel.misc
|
|||
|
|||
leap year problem.
Thank you all, it feels really good that there are good people like you taking time to help other people. I learnt more than solving my problem from this thread. Many thanks. "Rick Rothstein" wrote: That formula will work as long as the OP *never* hits a date in the year 2100. -- Rick (MVP - Excel) "Teethless mama" wrote in message ... Try this: =IF(MOD(YEAR(B1),4)=0,"L","NL") "2vao" wrote: to elaborate on my problem , cell b1 is formatted as mmm-yy so when I input Jan-08 it shows "L", if input any other year, like Jan-04 or Jan-12 or Jan-16 it shows "NL". "Rick Rothstein" wrote: Your formula appears to work for me... which years do you think it doesn't work for? In case you are interested, here is a shorter formula to do the same thing... =IF(MONTH(DATE(B1,2,29))=2,"L","NL") -- Rick (MVP - Excel) "2vao" wrote in message ... hello, I have this formula : =IF(OR(MOD(B1,400)=0,AND(MOD(B1,4)=0,MOD(B1,100)< 0)),"L","NL" (to show L if the date falls in a leap year). It works for 2008 only not for any other leap year. please help! thanks. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
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 | |||
Leap year date problem | Excel Worksheet Functions |