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

damian_r_Home

Board Regular
Joined
Jan 8, 2005
Messages
221
Office Version
  1. 365
  2. 2007
Platform
  1. Windows
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
Joined
Oct 14, 2016
Messages
3,661
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
Joined
Jan 8, 2005
Messages
221
Office Version
  1. 365
  2. 2007
Platform
  1. Windows
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

1625073749925.png


Hope this makes sense and that you can help.

Stay Safe.

D
 

Tetra201

MrExcel MVP
Joined
Oct 14, 2016
Messages
3,661
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
Joined
Aug 18, 2015
Messages
11,381

ADVERTISEMENT

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
Joined
Jan 8, 2005
Messages
221
Office Version
  1. 365
  2. 2007
Platform
  1. Windows
Tetra 201 & Eric W

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

Best regards

D
 

Tetra201

MrExcel MVP
Joined
Oct 14, 2016
Messages
3,661
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
Joined
Jan 8, 2005
Messages
221
Office Version
  1. 365
  2. 2007
Platform
  1. Windows
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.
 

Forum statistics

Threads
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.
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