Home |
Search |
Today's Posts |
#1
|
|||
|
|||
excel formula
6GN3839883
6G = NEVER CHANGES N = MONTH 1-9 JAN - SEP, O - D OCT - DEC 3 = YEAR 39883 = IS UNIQUE TO THE PRODUCT I WANT TO SET UP A FORMULA THAT WILL AUTOMATICALLY TELL ME IF THE PRODUCT IS WITHIN WARRANTY. IT CAN NOT BE ANY OLDER THAN 2 YEARS, I.E. 6GD901234 (6G DEC 1999) WOULD BE TO OLD SO IT HAS TO SHOW THAT IS WITHIN SAY 6G23????? AND 6G15?????. I HAVE TRIED USING 'IF' BUT I CAN NOT SEEM TO GET THE FORMULA RIGHT. IS THERE A FORMULA I CAN USE? |
#2
|
|||
|
|||
How would you display 1994, 2004 or 2009?
What I am meaning is how do you know (or how would excel know) 9 is 1999 and not 1909 or 2009? If there would be some system behind telling that years greater than 4 is 1990 + the year and less than or equal is 2000 plus the year, if so you can use something like =IF(DATE(IF(--MID(A1,4,1)4,MID(A1,4,1)+1990,MID(A1,4,1)+2000)+2 ,VLOOKUP(MID(A1,3,1),{"1",1;"2",2;"3",3;"4",4;"5", 5;"6",6;"7",7;"8",8;"9",9;"O",10;"N",11;"D",12},2, 0),1)<DATE(YEAR(TODAY()),MONTH(TODAY()),1),"No Warranty","Warranty") Regards, Peo Sjoblom Regards, Peo Sjoblom "BONNY" wrote: 6GN3839883 6G = NEVER CHANGES N = MONTH 1-9 JAN - SEP, O - D OCT - DEC 3 = YEAR 39883 = IS UNIQUE TO THE PRODUCT I WANT TO SET UP A FORMULA THAT WILL AUTOMATICALLY TELL ME IF THE PRODUCT IS WITHIN WARRANTY. IT CAN NOT BE ANY OLDER THAN 2 YEARS, I.E. 6GD901234 (6G DEC 1999) WOULD BE TO OLD SO IT HAS TO SHOW THAT IS WITHIN SAY 6G23????? AND 6G15?????. I HAVE TRIED USING 'IF' BUT I CAN NOT SEEM TO GET THE FORMULA RIGHT. IS THERE A FORMULA I CAN USE? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2003 FAILS, but Excel 2000 SUCCEEDS ??? | Excel Discussion (Misc queries) | |||
How do figure excel formula out? | Excel Discussion (Misc queries) | |||
How to I use ** without Excel thinking I want to type a formula? | Excel Discussion (Misc queries) | |||
Converting an Excel formula to an Access query formula | Excel Discussion (Misc queries) | |||
Preserve Excel formula entry | Excel Discussion (Misc queries) |