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

#### damian_r_Home

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

### Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

#### Tetra201

##### MrExcel MVP
The largest number of days occurs between the latest date and the earliest date in range M6:M1000, so
Excel Formula:
``=MAX(M6:M1000)-MIN(M6:M1000)``

#### damian_r_Home

##### Board Regular
Evening Tetra.
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

##### MrExcel MVP
See if the following array formula (to be entered using Ctrl+Shift+Enter, not just Enter) works for you:
Excel Formula:
``=MAX(SMALL(M6:M1000,ROW(INDIRECT("2:"&(COUNT(M6:M1000)))))-SMALL(M6:M1000,ROW(INDIRECT("1:"&(COUNT(M6:M1000)-1)))))``

#### Eric W

##### MrExcel MVP

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

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

#### damian_r_Home

##### Board Regular
Tetra 201 & Eric W

Best regards

D

#### Tetra201

##### MrExcel MVP
A compacted (and non-array?) version of formula from Post #4:
Excel Formula:
``=MAX(MMULT(SMALL(M6:M1000,{0,1}+ROW(INDIRECT("1:"&(COUNT(M6:M1000)-1)))),{-1;1}))``

#### damian_r_Home

##### Board Regular
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.

Replies
10
Views
291
Replies
1
Views
90
Replies
8
Views
124
Replies
7
Views
99
Replies
1
Views
142

1,140,925
Messages
5,703,183
Members
421,280
Latest member
Jaycee01

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