help with date formula

  • Thread starter Thread starter Victoria
  • Start date Start date
V

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
 
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
Click to expand...

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
 
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
 
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
Click to expand...

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 - thanks! That did the trick.
Victoria

Ron Rosenfeld said:
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
.
Click to expand...
 
Back
Top