help with date formula

[Victoria]

hello gurus

Imagine two columns (part of a larger table) , the first with dates, and the
second showing the difference (in days) between a date and the one above it.
My problem is that there are varying numbers blank rows between the dates.
I'd like it to look like this...

COLUMN A COLUMN B
Aug 27, 08 0

Dec 01, 08 96
Jan 21, 09 51



Jan 22, 09 1
Mar 08, 09 55

What formula could I use in the cells of Column B?

Much thanks
Victoria

 

[Ron Rosenfeld]

hello gurus

Imagine two columns (part of a larger table) , the first with dates, and the
second showing the difference (in days) between a date and the one above it.
My problem is that there are varying numbers blank rows between the dates.
I'd like it to look like this...

COLUMN A COLUMN B
Aug 27, 08 0

Dec 01, 08 96
Jan 21, 09 51



Jan 22, 09 1
Mar 08, 09 55

What formula could I use in the cells of Column B?

Much thanks
Victoria

A1: Aug 27, 08
B1: 0
B2: =IF(A2="","",A2-MAX($A$1:A1))
and fill B2 down as far as needed.

Assumes dates in A are in ascending order.
--ron

 

[Victoria]

hi Ron

Your solution works well! I've noticed that in my example, though, the
dates are always increasing when moving down the column. I guess that's why
the MAX function can be used the way you suggested. But there will be
instances where the dates could move back in time when moving down the
column. Any ideas how the formula could be modified to take this into
account?

Much thanks
Victoria

 

[Ron Rosenfeld]

hi Ron

Your solution works well! I've noticed that in my example, though, the
dates are always increasing when moving down the column. I guess that's why
the MAX function can be used the way you suggested. But there will be
instances where the dates could move back in time when moving down the
column. Any ideas how the formula could be modified to take this into
account?

Much thanks
Victoria

To pick up the last date when the dates in column A are unsorted, you could
use:

B2: =IF(A2="","",A2-LOOKUP(1E+307,$A$1:A1))

and fill down.

This could, of course, give a negative number as a result if the current date
is prior to the previously listed date.
--ron

 

[Victoria]

Ron - thanks! That did the trick.
Victoria

To pick up the last date when the dates in column A are unsorted, you could
use:

B2: =IF(A2="","",A2-LOOKUP(1E+307,$A$1:A1))

and fill down.

This could, of course, give a negative number as a result if the current date
is prior to the previously listed date.
--ron
.

 

[Ron Rosenfeld]

Ron - thanks! That did the trick.
Victoria

You're welcome. Thanks for the feedback.
--ron