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

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

kweaver

Well-known Member
Joined
May 8, 2018
Messages
2,134
Office Version
  1. 365
  2. 2010
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)
 

jrv924

New Member
Joined
Jun 10, 2021
Messages
8
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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!!!!!
 

kweaver

Well-known Member
Joined
May 8, 2018
Messages
2,134
Office Version
  1. 365
  2. 2010
If the manuf date is 12-2020, what is the expiration?
 

jrv924

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

ADVERTISEMENT

One year from that date. Sorry for not adding that in.
 

kweaver

Well-known Member
Joined
May 8, 2018
Messages
2,134
Office Version
  1. 365
  2. 2010
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")
 
Solution

jrv924

New Member
Joined
Jun 10, 2021
Messages
8
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
You are amazing. Thank you so much!!!!
 

Forum statistics

Threads
1,147,571
Messages
5,741,887
Members
423,693
Latest member
Excelquestion35

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
Top