Calculator End Of Warranty Date Based on Company Name

[mr_ITtoyou]

We have different companies that offer different amounts of days on their warranty. Company ABC offers 90 days warranty on their products, Company XYZ offers a 730-day on their warranty. How would the formula be setup using the companies name in cell A1, then in cell A2 the purchased date 3/21/21 add 90 days that display the end of warranty date on cell A3?
A1 A2 A3
ABC 3/21/21 6/19/21

B1 B2 B3
XYX 3/21/21 3/21/23

 

[jtakw]

Hi,

Just add the warranty days to purchase date:

Book3.xlsx
	A	B	C	D
1	Company	Warranty Days	Purchase Date	Warranty Expires
2	ABC	90	3/21/2021	6/19/2021
3	XYZ	730	3/21/2021	3/21/2023
Sheet870

Cell Formulas
Range	Formula
D2:D3	D2	=C2+B2

 

[jtakw]

In case you want it "hard-coded" in the formula, use E2 formula:

Book3.xlsx
	A	B	C	D	E
1	Company	Warranty Days	Purchase Date	Warranty Expires	OR like this
2	ABC	90	3/21/2021	6/19/2021	6/19/2021
3	XYZ	730	3/21/2021	3/21/2023
Sheet870

Cell Formulas
Range	Formula
E2	E2	=C2+IF(A2="ABC",90,IF(A2="XYZ",730,0))
D2:D3	D2	=C2+B2

 

[mr_ITtoyou]

In case you want it "hard-coded" in the formula, use E2 formula:

Book3.xlsx
	A	B	C	D	E
1	Company	Warranty Days	Purchase Date	Warranty Expires	OR like this
2	ABC	90	3/21/2021	6/19/2021	6/19/2021
3	XYZ	730	3/21/2021	3/21/2023
Sheet870

Cell Formulas
Range	Formula
E2	E2	=C2+IF(A2="ABC",90,IF(A2="XYZ",730,0))
D2:D3	D2	=C2+B2

Thank you for your help jtakw. Formula-E2 is what I was trying to figure out. That worked great.

E2

E2

=C2+IF(A2="ABC",90,IF(A2="XYZ",730,0))

 

[jtakw]

You're welcome, thanks for the feedback.

If you have Many Companies in your list, we can switch to a LOOKUP formula with or without a lookup table, rather than a bunch of nested IF(s), let me know.

 

[mr_ITtoyou]

jtakw, could you show me the formula LOOKUP for a list of companies? Thanks

 

[jtakw]

Please post a "dummy" sample of Companies and their warranty days. Thank You.

 

[mr_ITtoyou]

Please post a "dummy" sample of Companies and their warranty days. Thank You.

Companies Warranty Days
ACB_________________________60
Star Computer______________120
TV Supply___________________365
Data Supply_________________90

 

[jtakw]

Thanks for the info, here are a few options.

C2 formula uses VLOOKUP with the Table, main advantage over LOOKUP, Table Column G need Not be in Ascending order.
D2 formula uses LOOKUP with the Table, Table Column G Must be in Ascending order.
E2 formula uses LOOKUP Without Table, Values "hard-coded" in formula and Must be in Alpha ascending order. Disadvantage, hard to maintain when adding or changing Company Name and/or Warranty days change.

Advantage with using Table, easier to maintain when adding more companies and/or changing warranty days.

Let me know if you have questions:

Book3.xlsx
	A	B	C	D	E	F	G	H
1	Company	Purchase Date	Warranty Expires	Warranty Expires	Warranty Expires		Lookup Table
2	ACB	3/21/2021	5/20/2021	5/20/2021	5/20/2021		ACB	60
3	TV Supply	3/25/2021	3/25/2022	3/25/2022	3/25/2022		Data Supply	90
4	Star Computer	12/15/2020	4/14/2021	4/14/2021	4/14/2021		Star Computer	120
5	Data Supply	2/3/2021	5/4/2021	5/4/2021	5/4/2021		TV Supply	365
6	TV Supply	1/1/2021	1/1/2022	1/1/2022	1/1/2022
7
8			^Using Table	^Using Table	^Hard-coded
9			with VLOOKUP	with LOOKUP
Sheet877

Cell Formulas
Range	Formula
C2:C6	C2	=B2+VLOOKUP(A2,G$2:H$5,2,0)
D2:D6	D2	=B2+LOOKUP(A2,G$2:G$5,H$2:H$5)
E2:E6	E2	=B2+LOOKUP(A2,{"ACB","Data Supply","Star Computer","TV Supply"},{60,90,120,365})

 

[mr_ITtoyou]

Thanks for the info, here are a few options.

C2 formula uses VLOOKUP with the Table, main advantage over LOOKUP, Table Column G need Not be in Ascending order.
D2 formula uses LOOKUP with the Table, Table Column G Must be in Ascending order.
E2 formula uses LOOKUP Without Table, Values "hard-coded" in formula and Must be in Alpha ascending order. Disadvantage, hard to maintain when adding or changing Company Name and/or Warranty days change.

Advantage with using Table, easier to maintain when adding more companies and/or changing warranty days.

Let me know if you have questions:

Book3.xlsx
	A	B	C	D	E	F	G	H
1	Company	Purchase Date	Warranty Expires	Warranty Expires	Warranty Expires		Lookup Table
2	ACB	3/21/2021	5/20/2021	5/20/2021	5/20/2021		ACB	60
3	TV Supply	3/25/2021	3/25/2022	3/25/2022	3/25/2022		Data Supply	90
4	Star Computer	12/15/2020	4/14/2021	4/14/2021	4/14/2021		Star Computer	120
5	Data Supply	2/3/2021	5/4/2021	5/4/2021	5/4/2021		TV Supply	365
6	TV Supply	1/1/2021	1/1/2022	1/1/2022	1/1/2022
7
8			^Using Table	^Using Table	^Hard-coded
9			with VLOOKUP	with LOOKUP
Sheet877

Cell Formulas
Range	Formula
C2:C6	C2	=B2+VLOOKUP(A2,G$2:H$5,2,0)
D2:D6	D2	=B2+LOOKUP(A2,G$2:G$5,H$2:H$5)
E2:E6	E2	=B2+LOOKUP(A2,{"ACB","Data Supply","Star Computer","TV Supply"},{60,90,120,365})

That you for the huge help. This will help me a lot.