I need help in formula for tracking warranty validity based on shipping date and or sold date

MJ1119

New Member
Joined
Apr 20, 2020
Messages
1
Office Version
  1. 2019
Platform
  1. Windows
  2. Web
Hello Everyone!

I'm hoping that I can get some help with a formula on a spreadsheet I've been asked to work on.

We manufacture and sell industrial equipment imported from other country which has either 12 months warranty from shipping date for our customers to sell. So once its sold - warranty will be valid again for 12 months from sold date. I want to show in one column of the status of warranty as DISQUALIFIED or VALID

Can it be done so that when ever a sold date or shipping date is entered it will automatically show the status on Column F? Can it then be set so that the time remaining is then automatically shown in Col. G as an upto date and current

Layout of sheet is as follows:

A = Customer Name
B = Country
C = Serial number
D = Shipped Date
E = Sold date
F = Status
G= Warranty time left

If this is hard way of doing this then i'am open to any suggestions for better and / or easier ways.

Appreciate assistance! Many Thanks!
 

Some videos you may like

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,512
Office Version
  1. 365
Platform
  1. Windows
Welcome to the Board!

Try this (for row 2).
Formula for cell F2: =IF(AND(D2=0,E2=0),"",IF(EDATE(MAX(D2,E2),12)>TODAY(),"VALID","DISQUALIFIED"))
Formula for cell G2: =IF(AND(F2="VALID",MAX(D2,E2)>0),EDATE(MAX(D2,E2),12)-TODAY(),0)
and then copy the formulas down for all your rows.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,082
Messages
5,622,594
Members
415,909
Latest member
vbaBeginner94

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