Look at a series of dates

Sunline

Sunline

Well-known Member
Joined
Oct 6, 2007
Messages
701
Office Version
  1. 2016
Platform
  1. Windows
Hello all , I hoping to get a function that is able to look at a series of dates and produce a
result from 1 to 30 .

Col F always contains a date , these change daily .
Cols CH to DK also contain dates . There are x 30 cols of dates so min and max result answer
can only be between 1 and 30 . If cells are blank in cols CH to DK answer is always 1 one .
The dates are always in ascending order across each row in cols CH to DK . The newest date is always in col DK ,
next older date will always be to left in col DJ and so until last oldest date when horse began its first ever race start .

I want to determine how many races a horse has had since starting its campaign this time around .

I would like the function to find the first date where there was a break of 40 days or more from the date in col F and start
counting the following dates including adding 1 for today's date in col F.

I have highlighted the dates in green where the function should start the count (after) following the green shaded areas .
Thanks .
Excel Workbook
AFCHCICJCKCLCMCNCOCPCQCRCSCTCUCVCWCXCYCZDADBDCDDDEDFDGDHDIDJDKGOGP
1IDDateDate30Date29Date28Date27Date26Date25Date24Date23Date22Date21Date20Date19Date18Date17Date16Date15Date14Date13Date12Date11Date10Date9Date8Date7Date6Date5Date4Date3Date2Date1ResultHelp on result
218/06/201217/03/20113/04/201110/04/201116/04/20117/05/20115/06/201125/06/201110/07/201131/07/201113/08/201121/08/201127/11/201118/12/201130/12/201112/01/201228/01/20124/02/20123/06/20122120
328/06/201214/01/201221/04/20125/05/2012398
438/06/20127/04/20129/04/201222/04/201211/05/201218/05/201266th run including today 8/6/12
548/06/20129/04/201125/04/201130/07/20119/08/201112/09/201114/12/201115/01/201224/02/201216/03/201222/04/20123/05/2012793
658/06/20121First ever start
768/06/201211/06/201024/11/201022/12/20102/12/201116/12/201130/12/20111/02/20128/03/201216/03/201222/03/201229/03/20121/06/2012264
878/06/20121First ever start
Sheet1
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
Sort by date Sort by votes
Your data did not post corectly.
(same as me answer) but
Look at this and see if it helps:
Excel 2010
FCHCICJCKCLCMCNCOCPDBDCDDDEDFDGDHDIDJDKDL
16/8/20123/17/20124/3/201210-Apr30-May11-Jun12-Jun13-Jun14-Jun15-Jun16-Jun17-Jun18-Jun19-Jun20-Jun23
26/8/20123-Mar12-Jun13-Jun10-May15-Jun16-Jun17-Jun18-Jun19-Jun20-Jun21-Jun22
36/8/201210-Feb14-Jun11-May16-Jun17-Jun18-Jun19-Jun20-Jun21-Jun22-Jun10
46/8/20121-Jan17-Jun18-Jun19-Jun20-Jun21-Jun22-Jun23-Jun8
56/8/20123/11/20125/27/20125/28/20125/29/20125/30/20125/31/20126/1/20126/2/20126/3/20126/15/20126/16/20124107718-Jun19-Jun20-Jun21-Jun22-Jun23-Jun24-Jun30
66/8/20121-Jan2-Jan1-Mar21-Jun22-Jun23-Jun24-Jun25-Jun6
76/8/201210-Apr15-May20-Jun21-Jun22-Jun23-Jun24-Jun25-Jun26-Jun9
86/8/201225-Jun26-Jun

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet1​

Array Formulas
CellFormula
DL1{=IFERROR(30-MATCH(LARGE(IF(ABS($CH1:$DK1-(F1))>=40,$CH1:$DK1),1),$CH1:$DK1,0)+1,"")}

<thead>
</thead><tbody>
</tbody>
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>
 
Upvote 0
Hello Robert , Two things , when i posted this yesterday my post did display correctly as i always check for errors .
Im guessing there could be a fault with the new webby . I notice you are able to scroll across where mine has now vanished .

Thanks for your function , its not providing correct answers just yet , i will attach another Excel Jeanie HTML 4 if need be .
Thanks .
 
Upvote 0
I am reposting my example sheet again , just a smaller version to hopefully fit on the one web page .
This what the other end looked like , same questions apply as in my first post .
Thanks .
Excel Workbook
AFDDDEDFDGDHDIDJDKGOGP
1IDDateDate8Date7Date6Date5Date4Date3Date2Date1ResultHelp on result
218/06/201221/08/201127/11/201118/12/201130/12/201112/01/201228/01/20124/02/20123/06/20122120
328/06/201214/01/201221/04/20125/05/2012398
438/06/20127/04/20129/04/201222/04/201211/05/201218/05/201266th run including today 8/6/12
548/06/20129/08/201112/09/201114/12/201115/01/201224/02/201216/03/201222/04/20123/05/2012793
658/06/20121First ever start
768/06/201216/12/201130/12/20111/02/20128/03/201216/03/201222/03/201229/03/20121/06/2012264
878/06/20121First ever start
Sheet1
 
Upvote 0
another attempt , dont know why its not fitting onto web page .
Excel Workbook
EDEDFDGDHDIDJGNGO
1DateDate6Date5Date4Date3Date2Date1ResultHelp on result
28/06/201218/12/201130/12/201112/01/201228/01/20124/02/20123/06/20122120
38/06/201214/01/201221/04/20125/05/2012398
48/06/20127/04/20129/04/201222/04/201211/05/201218/05/201266th run
58/06/201214/12/201115/01/201224/02/201216/03/201222/04/20123/05/2012793
68/06/20121First ever start
78/06/20121/02/20128/03/201216/03/201222/03/201229/03/20121/06/2012264
88/06/20121First ever start
Sheet1
 
Upvote 0
Hello , sorry for the bump , hope post #5 explains things properly .
Thanks .
 
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Solved
Age Group Ranging by gender
Replies
2
Views
162
Raj1979
R
S
  • Solved
Count of active employees between dates.
Replies
3
Views
501
Fluff
Fluff
Dazzawm
  • Solved
Macro To Find Latest Date To Corresponding Data In Column B And Add To Sheet 2
Replies
7
Views
302
Fuji
F
T
  • Question
VBA help to count Daytime,Nighttime and Summary
2 3
Replies
20
Views
651
Alex Blakenburg
A
S
  • Question
Returning 1 or 0 based on multiple dates
Replies
2
Views
311
bebo021999
B

Forum statistics

Threads
1,221,567
Messages
6,160,540
Members
451,655
Latest member
rugubara

Share this page

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
Back
Top