listing gaps in dates

**shakes347** · 09-10-2010, 01:59 PM

Hey all,

I have a list of dates that has some gaps. I want a formula to look through these dates and then list the number of days in the gaps if the gap is greater than 35 days.

I have attached an example.

Thanks,

Shakes

**shakes347** · 09-10-2010, 02:00 PM

Any thoughts?

Thanks,

Shakes

**JBeaucaire** · 09-10-2010, 02:07 PM

There's a "key" column added to examine each range. You can hide that column if you wish.

The formula in D1 is an array formula, any changes must be confirmed with Control-Shift-Enter to keep the array active. Then copy the cell downward.

**shakes347** · 09-10-2010, 04:46 PM

This looks perfect, but when I when I try to adapt the code to another project I can't get it to work.

I have attached that project. I am trying to list the gaps in the dates that appear in Row O.

Your help is appreciated!

**JBeaucaire** · 09-10-2010, 06:16 PM

1) The formula in P22, then copied down:

=IF(O22="",0,O22-LOOKUP(99^99,$O$21:$O21))

2) The array entered formula in Q22 is:

=INDEX($P$1:$P$36, SMALL(IF($P$1:$P$36>35, ROW($P$1:$P$36), ""), ROW(A1)))

You had not used CTRL-SHIFT-ENTER to enter your incorrect version, so be sure to enter these properly to get the first array answer to appear. Then copy it down.