Warranty Validation based on serial number

jrv924

New Member
Joined
Jun 10, 2021
Messages
8
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Hello,

This is my first post, but I did some searching, and did not find specifically what I am looking for and wondering if I can have some help. We warranty products based on serial number, with a letter then numbers: N10201200799 which translates to:

N10 - model
2012 - Manufactured date (12-2020)
00799 - product serial number

I am trying to create a document that we can enter serial numbers in a column and have it display the information correctly and also state if the warranty is voided based on the manufactured date since warranty is good for one year. I am wondering if anyone smarter than me has an idea on what the best method could be used for this?

Thanks in advance!
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
Welcome to Mr. Excel.

Is this what you're looking for?

Duplicates (version 1).xlsb
ABCD
1Serial NumberModelManu YearProduct Serial Number
2N10201200799N1012-202000799
Sheet1
Cell Formulas
RangeFormula
B2B2=LEFT(A2,3)
C2C2=MID(A2,6,2)&"-20"&MID(A2,4,2)
D2D2=RIGHT(A2,5)
 
Upvote 0
That is definitely looks good!! If I wanted to create another column that says if the the device warranty is expired based on column C, what would be the easiest way to do this?

Thank you!!!!!
 
Upvote 0
If the manuf date is 12-2020, what is the expiration?
 
Upvote 0
One year from that date. Sorry for not adding that in.
 
Upvote 0
Maybe (based on today's date):

Duplicates (version 1).xlsb
ABCDEF
1Serial NumberModelManu YearProduct Serial NumberExpiration
2N10201200799N1012-20200079912-2021OK
3N10200200999N1002-20200099902-2021Expired
Sheet1
Cell Formulas
RangeFormula
B2:B3B2=LEFT(A2,3)
C2:C3C2=MID(A2,6,2)&"-20"&MID(A2,4,2)
D2:D3D2=RIGHT(A2,5)
E2:E3E2=LEFT(C2,2)&"-"&1+RIGHT(C2,4)
F2:F3F2=IF(DATE(RIGHT(E2,4),LEFT(E2,2),1)>TODAY(),"OK","Expired")
 
Upvote 0
Solution

Forum statistics

Threads
1,213,567
Messages
6,114,342
Members
448,570
Latest member
rik81h

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top