help with date formula
 

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

help with date formula
 

On Sun, 29 Nov 2009 16:27:01 -0800, Victoria
wrote:

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

help with date formula
 

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" wrote:

On Sun, 29 Nov 2009 16:27:01 -0800, Victoria
wrote:

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
.

help with date formula
 

On Sun, 29 Nov 2009 18:37:01 -0800, Victoria
wrote:

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

help with date formula
 

Ron - thanks! That did the trick.
Victoria

"Ron Rosenfeld" wrote:

On Sun, 29 Nov 2009 18:37:01 -0800, Victoria
wrote:

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
.

help with date formula
 

On Sun, 29 Nov 2009 20:18:01 -0800, Victoria
wrote:

Ron - thanks! That did the trick.
Victoria

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