Formula help with tracking warranty

[KC0713]

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

 

[Eric W]

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:

	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Customer Name	Brand	Products Code	Serial Number	Date Sold	Warranty Term	Waranty Ended		Brand	Warranty Length		Wholesale customers
2	Mary Smith	aaa	111	111	1/2/2019	60	Valid		xyz	365		Joe's Plumbing
3	Mark Jones	xyz	222	222	7/1/2018	365	Valid		wxy	365
4	Joe's Plumbing	ccc	333	333	10/1/2018	90	Not valid		vwx	730
5	Amy Adams	ddd	444	444	12/24/2018	60	Valid		uvw	180

<tbody>
</tbody>

Sheet10​

Worksheet Formulas Cell Formula 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]

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]

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

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Customer Name	Brand	Products Code	Serial Number	Date Sold	Warranty Term	Waranty Ended		Brand	Product	Wholesale Warranty Length	Retail Warranty Length		Wholesale customers
2	Mary Smith	aaa	111	111	1/20/2019	45	Valid		xyz	123	90	60		Joe's Plumbing
3	Mark Jones	xyz	222	222	7/1/2018	60	Not valid		xyz	245	0	0
4	Joe's Plumbing	xyz	245	333	10/1/2018	0	Not valid		vwx	default	365	180
5	Amy Adams	ddd	444	444	12/24/2018	90	Valid		uvw	default	180	90
6	Joe's Plumbing	xyz	555	555	1/1/2019	90	Valid		xyz	default	90	60
7	Ed Smith	qqq	666	666	10/1/2018	Not found	None		aaa	default	90	45
8									ddd	default	0	90
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 Cell Formula G2 =IFERROR(IF(TODAY()>E2+F2,"Not valid","Valid"),"None") <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Array Formulas Cell Formula 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]

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]

I'd do something like this:

Excel 2012

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Customer Name	Brand	Products Code	Serial Number	Date Sold	Exchange	Warranty Term	Waranty Ended		Brand	Product	Wholesale Warranty Length	Retail Warranty Length		Wholesale customers
2	Mary Smith	aaa	111	111	1/20/2019		45	Not valid		xyz	123	90	60		Joe's Plumbing
3	Mark Jones	xyz	222	222	7/1/2018		60	Not valid		xyz	245	0	0
4	Joe's Plumbing	xyz	245	333	10/1/2018		0	Not valid		vwx	default	365	180
5	Amy Adams	ddd	444	444	12/24/2018		90	Valid		uvw	default	180	90
6	Joe's Plumbing	xyz	555	555	1/1/2019	yes	90	Not valid		xyz	default	90	60
7	Ed Smith	qqq	666	666	10/1/2018		Not found	None		aaa	default	90	45
8										ddd	default	0	90

<tbody>
</tbody>

Sheet2​

Worksheet Formulas Cell Formula H2 =IFERROR(IF(OR(TODAY()>E2+G2,F2="yes"),"Not valid","Valid"),"None") <tbody> </tbody>

<tbody>
</tbody>

Array Formulas Cell Formula 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.

 

[KC0713]

Thanks for all the help.

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

 

[Eric W]

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.