# Problem With Date Column

#### CARBOB

##### Well-known Member
This is the problem. I have a formula that counts the number of draws that a 3 digit number has missed since it's been drawn. I am getting a negative number for the count. I'm not sure if it's because the sheet I'm referenced too has many dates the same or if it's because the dates are in ascending order. The formula works great on other worksheets with dates in descending order. The data on the sheet "MIRRORSMATESTOTALS" is created by a macro with data from another sheet. Is there any way the formula can be altered? Thanks for any suggestions. I have posted both sheets.

Carbob
DOUBLESMIRRORSTOTALS MASTER.xls
ABCD
4COMBOHITSSKIPS
500137-63
601037-22
710038-40
8002
FILTERS
DOUBLESMIRRORSTOTALS MASTER.xls
ABCD
1DATEDRAWMATEMIRROR
201/01/03147692
301/01/03559995004
401/01/03741296
501/01/03148693
601/01/03706251
701/01/03966699411
MIRRORSMATESTOTALS

### Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
bUMP

CARBOB

It looks like your formula basically subtracts a date found in column A from the date in \$A\$2. Since the dates are ascending, you are subtracting a larger number from a smaller number and so it's not surprising that the result is negative.

That problem could easily be solved by changing the order of the subtraction. However, subtracting the date in \$A\$2 from the date the combination was last drawn isn't going to give you the answer you want as far as I can see.

What I think you want is the last date in column A minus the last date the combination was drawn. The only way I can think of to do that will make your sheet VERY slow to calculate if you have 48,165 rows as your formula suggests. Try this with a smaller range first.

I have done this on one sheet only for simplicity in testing. Formula in F2 (copied down) is:
=IF(ISNUMBER(MATCH(2,1/(B\$2:B\$100=B2))),LOOKUP(9.99999999999999E+307,A\$2:A\$100)-INDEX(A\$2:A\$100,MATCH(2,1/(B\$2:B\$100=B2))),"")
Confirmed with CTRL+SHIFT+ENTER
Mr Excel.xls
ABCDEF
1DateDrawSkips
201/01/20031232
301/01/20034564
401/01/20038561
502/01/20039993
602/01/20036543
702/01/20033213
803/01/20031592
903/01/20039512
1003/01/20031232
1104/01/20035681
1204/01/20038561
1304/01/20038881
1405/01/20037020
1505/01/20032030
16
Skips

Peter,
I need the formula on the "Filters" sheet to reference the 3 digit nubers in col "A" and the dates are on the "Mirrorsmatestotals" sheet. Here's what I got. I probably have something wrong, no doubt.

Carbob
DOUBLESMIRRORSTOTALS MASTER.xls
ABCD
4COMBOHITSSKIPS
50013737624
601037-22
710038-40
FILTERS

CARBOB

I am unable to access the formula you have in column C in your last post.

Yes, I understand about referencing a different sheet in your situation but thought you might be able to adapt what I gave you. I will modify it if required, but did my previous sample post give the results you would expect from my sample data?

Disreguard Peter works like a champ. Sometimes this "Ole" man can't see the forest for the trees. Thanks a lot.

Carbob

Replies
3
Views
129
Replies
1
Views
135
Replies
1
Views
140
Replies
3
Views
304
Replies
5
Views
210

1,203,552
Messages
6,056,059
Members
444,841
Latest member
SF_Marnie

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