Problem With Date Column

CARBOB

Well-known Member
Joined
Jun 6, 2005
Messages
1,860
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
 

Some videos you may like

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,483
Office Version
  1. 365
Platform
  1. Windows
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
 

CARBOB

Well-known Member
Joined
Jun 6, 2005
Messages
1,860
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
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,483
Office Version
  1. 365
Platform
  1. Windows
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?
 

CARBOB

Well-known Member
Joined
Jun 6, 2005
Messages
1,860
Disreguard Peter works like a champ. Sometimes this "Ole" man can't see the forest for the trees. Thanks a lot.

Carbob
 

Watch MrExcel Video

Forum statistics

Threads
1,118,226
Messages
5,570,990
Members
412,353
Latest member
SofiaV
Top