# date Subtraction with serial numbers as a reference

#### queysoft

##### New Member
Hello - Stuck with this one - I'm sure its simple and i am once again overthinking......

So, Column A = List of serial numbers (some unique, some duplicated several times) - ALL IN ORDER.
Column B = Date

What I want, is a formula that subtracts the LAST date from the FIRST date for each serial number to get a max number of days for each serial number.

The sheet is sorted by serial and then by date descending.

So the formula would look at the serials, and pick out the last and first date for each serial number. For the unique serials of course the return would be a zero.

Any ideas?

#### Attachments

• Screenshot_example_0812.jpg
109.2 KB · Views: 12

### Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
Excel Formula:
``=IF(COUNTIFS(A\$2:A2,A2)=1, AGGREGATE(14,6,\$B\$2:\$B\$100/(\$A\$2:\$A\$100=A2),1)-B2,"")``

you are showing 2016 version in profile
so a MINIFS and a MAXIFS maybe the way to go by ID number

As its an image i cannot test - so may setup if i have time or use XL2BB to post a sample - see signature / menu

@etaf both minifs & maxifs are only available with 2019 or newer.

Thanks @Fluff, I thought 2016 version had them as i searched google to see
Cant trust the internet (where have i heard that before)

Good work chaps! The first solution works a treat…..Correct also about MAXIFS not working. Work won’t shell out for O365 nor upgrade me, yet I’m asked to do stuff like this. Plus of course GDPR prevents me using my personal laptop. Hey ho.

Also why it was just a screen shot and not the sheet (download permissions etc to install what’s needed)

Glad we could help & thanks for the feedback.

Replies
1
Views
97
Replies
5
Views
335
Replies
6
Views
212
Replies
7
Views
573
Replies
4
Views
337

1,203,059
Messages
6,053,301
Members
444,650
Latest member
bookendinSA

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