# Formula help with tracking warranty

#### KC0713

##### New Member
Hi all,

I'm hoping that I can get some help with a formula on a spreadsheet.
I'm creating an excel that tracks return products base on the serial number.

Here is the layout of the spreadsheet.
Sheet 1: Serial Number
A = Customer Name
B = Brand
C = Products Code
D = Serial Number
E = Date Sold
F = Warranty Term (60 days, 90 days, 1 year)
G= Warranty Ended

Sheet 2: Return Product
A = Customer Name
B = Brand
C = Products Code
D = Serial Number
E = Date Return
F=Warranty Ended
G=Reason

For sheet 1 Is it possible to have a formula automatically calculate the term period in cell F? The term would be base on the brand and customer field.

Conditions for formula need.
Warranty term:
Retail- 60 days
Wholesale-90 days
Brand: XYZ- 1 year

For sheet 2

If serial number match then subtracts return date from the sold day and show if warranty "still valid" or the end date of the warranty. Else, show "Not Valid".

### Excel Facts

Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
Welcome to the forum.

You say that the term is based on brand, or if it's retail/wholesale. I don't see how you determine that. If you have a table to define those things, you could do something like this:

ABCDEFGHIJKLM
1Customer NameBrandProducts CodeSerial NumberDate SoldWarranty TermWaranty EndedBrandWarranty LengthWholesale customers
2Mary Smithaaa1111111/2/201960Validxyz365Joe's Plumbing
3Mark Jonesxyz2222227/1/2018365Validwxy365
4Joe's Plumbingccc33333310/1/201890Not validvwx730

<tbody>
</tbody>
Sheet10

Worksheet Formulas
CellFormula
F2=IFERROR(VLOOKUP(B2,\$I\$2:\$J\$5,2,0),IF(ISNUMBER(MATCH(A2,\$L\$2:\$L\$10,0)),90,60))
G2=IF(TODAY()>E2+F2,"Not valid","Valid")

<tbody>
</tbody>

<tbody>
</tbody>

Update the tables in I:J and L as needed. For Sheet2, you can easily do a VLOOKUP or INDEX/MATCH using the serial number to get the warranty ended field. Notice that I used number of days everywhere instead of "60 days" or "1 year" since it makes things much easier.

Thanks for the help.

Actually, the term would be base on the brand, products code, and customer. There are certain products we don't provide warranty and wholesale customer have a longer warranty period than retail customer.

For example, brand BR warranty is 90 days. The same brand, Product code 245 have no warranty. Retail customer 60 days, wholesale customer 90 days.

So from your example, Joe's plumbing is a wholesale customer. The warranty would be 90 days if he buy any product from brand BR except for product code 245.

Is it possible to add those criteria into F2 formula?

I still don't have a clear picture of how you assign warranties, however, this is my latest take on it:

ABCDEFGHIJKLMNO
1Customer NameBrandProducts CodeSerial NumberDate SoldWarranty TermWaranty EndedBrandProductWholesale Warranty LengthRetail Warranty LengthWholesale customers
2Mary Smithaaa1111111/20/201945Validxyz1239060Joe's Plumbing
3Mark Jonesxyz2222227/1/201860Not validxyz24500
4Joe's Plumbingxyz24533310/1/20180Not validvwxdefault365180
6Joe's Plumbingxyz5555551/1/201990Validxyzdefault9060
8ddddefault090
9

</tbody>
Sheet10

Worksheet Formulas
CellFormula
G2=IFERROR(IF(TODAY()>E2+F2,"Not valid","Valid"),"None")

</tbody>

<tbody>
</tbody>

Array Formulas
CellFormula

</tbody>
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

In this version, you still have 2 tables. The table in N is a list of your wholesale customers. The table in I:L has the warranties. Brand and product go in I:J, and the wholesale period for wholesale/retail goes in K:L. If the actual product number is found, it takes that (like xyz/245). If not found, it next looks for the brand and the word "default" and takes that.

If I add a row in before F for Exchange. If the row for Exchange is "yes" warranty: Expire. If "no" continue with the next criteria. Should I add an If Function in the F2 formula? Or how would that work?

Thanks

I'd do something like this:

Excel 2012
ABCDEFGHIJKLMNO
1Customer NameBrandProducts CodeSerial NumberDate SoldExchangeWarranty TermWaranty EndedBrandProductWholesale Warranty LengthRetail Warranty LengthWholesale customers
2Mary Smithaaa1111111/20/201945Not validxyz1239060Joe's Plumbing
3Mark Jonesxyz2222227/1/201860Not validxyz24500
4Joe's Plumbingxyz24533310/1/20180Not validvwxdefault365180
6Joe's Plumbingxyz5555551/1/2019yes90Not validxyzdefault9060
8ddddefault090

<tbody>
</tbody>
Sheet2

Worksheet Formulas
CellFormula
H2=IFERROR(IF(OR(TODAY()>E2+G2,F2="yes"),"Not valid","Valid"),"None")

<tbody>
</tbody>

<tbody>
</tbody>

Array Formulas
CellFormula

<tbody>
</tbody>
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

You'd just have to add the Exchange column, then change the (now) H2 formula.

Last edited:
Thanks for all the help.

But is it possible to change the warranty term to "0" if Exchange row is "yes"?

Sure, just change the G2 formula (based on the layout in post 6) to:

still with Control+Shift+Enter.

Replies
4
Views
1K
Replies
6
Views
256
Replies
4
Views
606
Replies
2
Views
215
Replies
1
Views
155

1,218,899
Messages
6,145,097
Members
450,590
Latest member
Naneng

### 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.

### Which adblocker are you using?

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

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