# how to count the days between 2 dates based on the unique number

#### Seifeddine87

##### Board Regular
hi
how to count the days between 2 dates based on the unique number
Example

Sale sheet

........A................B ........................C
1.....Date.......Item Number........ No Of Days..
2 28/12/16..........3TU55...................23
3 25/12/16..........K55P3...................18
4 24/12/16..........K8Y44...................19
5
6

Stock sheet

........A................B
1.....Date.......Item Number
2 07/12/16..........K55P3
3 05/12/16..........K8Y44
4 05/12/16..........3TU55
5
6
Thank you

### Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

##### MrExcel MVP
In C2 of Sale enter and copy down:

=A2-LOOKUP(9.99999999999999E+307,1/(stock!\$B\$2:\$B\$4=B2),stock!\$A\$2:\$A\$4)

Format the formula cells as appropriate.

#### JLGWhiz

##### Well-known Member
Assuming that the item number will only appear once on each sheet:
Macro.
Code:
``````Sub daysgone()
Dim c As Range, fn As Range
With Sheets("Sale") 'Edit sheet name
For Each c In .Range("B2", .Cells(Rows.Count, 2).End(xlUp))
Set fn = Sheets("Stock").Range("B:B").Find(c.Value, , xlValues, xlWhole) 'Edit sheet name
If Not fn Is Nothing Then
c.Offset(, 1) = fn.Offset(, -1).Value - c.Offset(, -1).Value
End If
Next
End With
End Sub``````

#### Seifeddine87

##### Board Regular
sorry but why 9.99999999999999E+307,1 just i want to understand it thank you for your reply

##### MrExcel MVP
sorry but why 9.99999999999999E+307,1 just i want to understand it thank you for your reply

The LOOKUP bit picks out the last date (number/numeric value) associated with an item. To that end, a limit indicating constant of Excel is used as the look up value.

See:
http://www.mrexcel.com/forum/excel-questions/102091-9-9999999-a.html
http://www.mrexcel.com/forum/excel-questions/99621-lookup-value-unsorted-data.html#post492425
http://www.mrexcel.com/forum/excel-...tiple-matches-match-returned.html#post1523998

#### T. Valko

##### Well-known Member
sorry but why 9.99999999999999E+307
You can replace that long number with the number 2.

#### Seifeddine87

##### Board Regular
In C2 of Sale enter and copy down:

=A2-LOOKUP(9.99999999999999E+307,1/(stock!\$B\$2:\$B\$4=B2),stock!\$A\$2:\$A\$4)

Format the formula cells as appropriate.
hi its work perfect thank you
could you tell me why 9.99999999999999E+307,1/ just to understand it

Replies
2
Views
250
Replies
2
Views
245
Replies
4
Views
154
Replies
17
Views
392
Replies
4
Views
127

1,190,870
Messages
5,983,325
Members
439,839
Latest member
iblackie

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

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