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? |
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? |
All times are GMT +1. The time now is 08:14 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com