Break of 40 days

[Sunline]

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
	E	DE	DF	DG	DH	DI	GM
1	Date	Date5	Date4	Date3	Date2	Date1	Result
2	8/06/2012	30/12/2011	12/01/2012	28/01/2012	4/02/2012	3/06/2012	2
3	8/06/2012		14/01/2012	15/04/2012	21/04/2012	5/05/2012	4
4	8/06/2012						1
5	8/06/2012	8/03/2012	16/03/2012	22/03/2012	29/03/2012	1/06/2012	2
6	8/06/2012						1
Sheet1

 

[TKB]

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.

 

[Sunline]

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 .

 

[barry houdini]

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

 

[Sunline]

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 .

 

[barry houdini]

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)

 

[Sunline]

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