Largest period between two dates in a column containing numerous dates.

[damian_r_Home]

Good evening all.

I have a sheet in which column M has numerous dates in it.
All the dates are in order oldest and the top, newest at the bottom.
However the dates are not manually entered, they are there based on an IF formula which is =IF(C6="N",A6,"") which will obviously leave a lot of cells without a date value in them.

In cell M5 I'm looking for a formula that will scan all the dates in range M6:M1000 and to display the largest number of days that occurs between the dates.....

Hope this makes sense

stay safe

D

 

[Tetra201]

The largest number of days occurs between the latest date and the earliest date in range M6:M1000, so

=MAX(M6:M1000)-MIN(M6:M1000)

 

[damian_r_Home]

Evening Tetra.
Thanks for your reply.
The formula you have offered only counts the days between largest and smallest when actually I'd like it to look down the column of dates and based on the screenshot below return the value of 70 as that is the largest number of days between any two consecutive dates

Hope this makes sense and that you can help.

Stay Safe.

D

 

[Tetra201]

See if the following array formula (to be entered using Ctrl+Shift+Enter, not just Enter) works for you:

=MAX(SMALL(M6:M1000,ROW(INDIRECT("2:"&(COUNT(M6:M1000)))))-SMALL(M6:M1000,ROW(INDIRECT("1:"&(COUNT(M6:M1000)-1)))))

 

[Eric W]

Non-array formula, but it requires AGGREGATE, first available in Excel 2010:

=AGGREGATE(14,6,LARGE(M6:M1000,ROW(1:999))-LARGE(M6:M1000,ROW(2:1000)),1)

 

[damian_r_Home]

Tetra 201 & Eric W

Thankyou for your help, I shall try both your replies tomorrow.

Best regards

D

 

[Tetra201]

A compacted (and non-array?) version of formula from Post #4:

=MAX(MMULT(SMALL(M6:M1000,{0,1}+ROW(INDIRECT("1:"&(COUNT(M6:M1000)-1)))),{-1;1}))

 

[damian_r_Home]

Morning All.

Sorry for not getting back to you sooner, only work and home life have been hectic.

Tetra201, thanks for your alternative suggestion - i will also be trying that.