Formula help with tracking warranty

KC0713

New Member
Joined
Feb 21, 2019
Messages
4
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".


Any Suggestions? Thanks in Advance
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,821
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
5Amy Adamsddd44444412/24/201860Validuvw180

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

KC0713

New Member
Joined
Feb 21, 2019
Messages
4
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?
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,821
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
5Amy Adamsddd44444412/24/201890Validuvwdefault18090
6Joe's Plumbingxyz5555551/1/201990Validxyzdefault9060
7Ed Smithqqq66666610/1/2018Not foundNoneaaadefault9045
8ddddefault090
9

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet10

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

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>

Array Formulas
CellFormula
F2{=IFERROR(INDEX($K$2:$L$10,IFERROR(MATCH(B2&"|"&C2,$I$2:$I$20&"|"&$J$2:$J$20,0),MATCH(B2&"|default",$I$2:$I$20&"|"&$J$2:$J$20,0)),2-COUNTIF($N$2:$N$10,A2)),"Not found")}

<thead>
</thead><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>



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.
 

KC0713

New Member
Joined
Feb 21, 2019
Messages
4

ADVERTISEMENT

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
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,821
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
5Amy Adamsddd44444412/24/201890Validuvwdefault18090
6Joe's Plumbingxyz5555551/1/2019yes90Not validxyzdefault9060
7Ed Smithqqq66666610/1/2018Not foundNoneaaadefault9045
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
G2{=IFERROR(INDEX($L$2:$M$10,IFERROR(MATCH(B2&"|"&C2,$J$2:$J$20&"|"&$K$2:$K$20,0),MATCH(B2&"|default",$J$2:$J$20&"|"&$K$2:$K$20,0)),2-COUNTIF($O$2:$O$10,A2)),"Not found")}

<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:

KC0713

New Member
Joined
Feb 21, 2019
Messages
4
Thanks for all the help.

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

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,821
Sure, just change the G2 formula (based on the layout in post 6) to:

=IF(F2="yes",0,IFERROR(INDEX($L$2:$M$10,IFERROR(MATCH(B2&"|"&C2,$J$2:$J$20&"|"&$K$2:$K$20,0),MATCH(B2&"|default",$J$2:$J$20&"|"&$K$2:$K$20,0)),2-COUNTIF($O$2:$O$10,A2)),"Not found"))

still with Control+Shift+Enter.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,798
Messages
5,638,392
Members
417,025
Latest member
MusterDuster

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