# Break of 40 days

#### Sunline

##### Well-known Member
Hello all , I am wanting to get a function that can find the first break of 40 days or more .

Col F contains dates as does cols CH too DK .

Asumming col F is todays date , col DK will have the closest most previous date , col DJ
would have the second closest most previous date to col F and so on , col CH would
contain the oldest most previous date to col F , otherwords runs backwards .

There will never be blank cells in between dates .

If cols CH to DK are blank then result answer is always 1 .

I want to find the first break of 40 days or more from col F looking at the dates going
backwards from cols DK to CH .

Once a break of 40 days or more is found count all the dates up to col DK and add 1 for today .

Example : Todays date is 21/6/12_______ 1/1/12-----1/4/12-----1/5/12-----1/6/12-----7/6/12

There is a break of 91 days between 1/1/12 and 1/4/12 , so answer is 5 , this includes 1/4/12

Another example sheet below .
Thanks .
Excel Workbook
EDEDFDGDHDIGM
1DateDate5Date4Date3Date2Date1Result
28/06/201230/12/201112/01/201228/01/20124/02/20123/06/20122
38/06/201214/01/201215/04/201221/04/20125/05/20124
48/06/20121
58/06/20128/03/201216/03/201222/03/201229/03/20121/06/20122
68/06/20121
Sheet1

### Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
Im slowly formulating a macro that seems to be working on everything except your current date format. Is there any chance that it could be formatted to show the dates as mm/dd/yyyy instead of dd/mm/yyyy?

If so, i could probably have something fairly quickly for you.

Hello TKB, sorry for late reply , yes changing the dates to mm/dd/yyyy would be fine , a macro to do this would be excellent .
Thanks .

Perhaps a formula.....

Try this in GM2 copied down

=IFERROR(IF(F2-DK2>=40,1,COLUMNS(CH2:DK2)+1-MATCH(2,INDEX(1/(CI2:DK2-CH2:DJ2>=40)/(CH2:DJ2<>""),0))),"no match")

I assume that the result will be 1 if the date in DK2 is 40 days or more before today.....and also you get "no match" if there are dates but no 40 day gaps

Thanks barry , the function is working well .

Yes result will be 1 if the date in DK2 is 40 days or more before today .

"no match" , i failed to account for this , as there are no gaps at all of 40 days or more i would need the function to count all dates in cell range CH:DK plus 1 , basically it would count every filled cell in this range and add 1 .
Thanks .

OK then you can just change the "no match" part to show a count of dates in CH2:DK2 + 1, i.e.

=IFERROR(IF(F2-DK2>=40,1,COLUMNS(CH2:DK2)+1-MATCH(2,INDEX(1/(CI2:DK2-CH2:DJ2>=40)/(CH2:DJ2<>""),0))),COUNT(CH2:DK2)+1)

Thanks for the function barry houdini , i will check it against my stats . Thanks again .

Replies
1
Views
181
Replies
4
Views
211
Replies
9
Views
161
Replies
1
Views
114
Replies
11
Views
388

1,203,094
Messages
6,053,506
Members
444,667
Latest member
KWR21

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