Break of 40 days

Sunline

Well-known Member
Joined
Oct 6, 2007
Messages
675
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
 

Some videos you may like

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

TKB

Board Regular
Joined
Aug 18, 2011
Messages
128
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

Well-known Member
Joined
Oct 6, 2007
Messages
675
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

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
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

Well-known Member
Joined
Oct 6, 2007
Messages
675

ADVERTISEMENT

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

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
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

Well-known Member
Joined
Oct 6, 2007
Messages
675
Thanks for the function barry houdini , i will check it against my stats . Thanks again .
 

Watch MrExcel Video

Forum statistics

Threads
1,123,057
Messages
5,599,545
Members
414,316
Latest member
ExcelLee

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
Top