Excel count consecurive dates in a range of dates, ignoring blank cells

LaraF

New Member
Joined
Aug 30, 2020
Messages
4
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
HI, I need some help please. I have a list of dates in column A that are in ascending order, however sometimes are seperated by blank cells.
I need column B to count how many occurences of consectutive dates occur after the date in Column A. Can anyone please help me. Thanks

CURRENT DATECount No. of consectutive dates after current date in A
01/08/202
05/08/20
07/08/202
08/08/201
10/08/201
11/08/200
 

Some videos you may like

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows
Welcome to the MrExcel board!

Could you have data like this?
If so, would you want those coloured cells to count as
- one lot 3 consecutive dates, or
- two lots of consecutive dates (7th & 8th being consecutive and 8th & 9th being consecutive)?

BTW, I suggest that you investigate XL2BB for providing sample data & expected results.

LaraF 1.xlsm
A
1CURRENT DATE
21/08/2020
3
45/08/2020
57/08/2020
6
78/08/2020
89/08/2020
9
1011/08/2020
Sheet1
 

LaraF

New Member
Joined
Aug 30, 2020
Messages
4
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
HI Peter,

Thanks for responding and I have install XL2BB as suggested, although still learning.....

It could def appear as your example and if so then I would want column B to show two lots of consecutive dates. Cells B2, b4 & B5 should all show the number 2 as there are two lots of cosecutive dates after the corresponding cell A date. Cell B7 would show only 1 and Cell B8 and B10 would show 0. As more dates are added to column A the results in Column B increase accordinly.

10/08/202
13/08/202
15/08/202
17/08/202
18/08/201
19/08/200


Here is the actual data in Column AE with the results that I am trying to get in AF.
31 August 2020.jpg
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows
I forgot to ask if you have the FILTER and SEQUENCE functions in your version of Excel 365 (not everybody has)?
Also forgot to ask if you care if this is a formula, macro or power query solution?

Assuming you do have the functions I mentioned above, see if something like this would work for you.

LaraF 1.xlsm
AEAF
1
2
3
410/08/20202
5 
6 
713/08/20202
8 
915/08/20202
10 
1117/08/20202
12 
1318/08/20201
1419/08/20200
Sheet2
Cell Formulas
RangeFormula
AF4:AF14AF4=IF(AE4="","",SUM(FREQUENCY(IF(IFERROR(FILTER(AE5:AE$20,AE5:AE$20<>"")-FILTER(AE4:AE$19,AE4:AE$19<>""),0)=1,SEQUENCE(COUNT(AE5:AE$20))),IF(IFERROR(FILTER(AE5:AE$20,AE5:AE$20<>"")-FILTER(AE4:AE$19,AE4:AE$19<>""),1)<>1,SEQUENCE(COUNT(AE4:AE$19))))))
 

LaraF

New Member
Joined
Aug 30, 2020
Messages
4
Office Version
  1. 365
  2. 2016
Platform
  1. Windows

ADVERTISEMENT

I've updated my settings. Excel 2016 on Office 365. Any help is greatly appreciated. Thanks again
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows
Excel 2016 on Office 365
Not sure what you mean by that. In Excel File -> Account
What do you see here?

1598851624612.png



And checking for the FILTER function.
If you click the fx button and choose 'Lookup & Reference' is FILTER in the list?

1598851825405.png
 

LaraF

New Member
Joined
Aug 30, 2020
Messages
4
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Peter SSS thanks heaps ! that worked perfectly ! Your a legend.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,464
Messages
5,596,288
Members
414,052
Latest member
Dual Showman

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