Thanks, I am getting closer! This worked, but now I am realizing that it is
a bit more complicated than that. So let's suppose I have:

D10=N/A
E10=4/14/2005
so F10= N/A (formula of E10-D10)

then...
H10=8/29/2005
I10= N/A
so I need J10 to calculate H10-E10.

I need the formula in J10 (and all other columns like this one in the sheet)
to use the most recent date and subtract the next most recent date from it,
even if it's not in the column right next to it. Like, if I10 DID have a
value in it, I would want J10 to have I10-H10, because that's the most recent
date minus the next most recent date. But if, for example, H10 had N/A and
I10 had 8/29/2005, I would want J10 to calucate I10 minus E10, because that
would be the most recent minus the next most recent.

If both H10 and I10 were N/A, I would want the formula in J10 to compute
"N/A" because the most recent time difference would already be accounted for
in F10. In this same scenario, if E10 was 4/14/2005, H10 and I10 were N/A,
and L10 was 8/20/2006, I would want the formula in N10 to calucate L10 minus
E10, or the closest date to the most recent date.

i dont know if that makes any sense. I really appreciate your help so far.
i am so close to having it how i want!

"T. Valko" wrote:

these dates will always be sequential
so the latter is always more recent

Maybe this:

=IF(COUNT(L10),L10-MAX(H10,I10),"N/A")

--
Biff
Microsoft Excel MVP


"sarajane82" wrote in message
...
I will try! For example, if there is a date in H10, but not in I10, it
will
come up with N/A. But then later on in the spreadsheet, there may be a
date
in L10 (these dates will always be sequential, so the latter is always
more
recent). So I want a formula that will take L10 and subtract from it the
most recent date before it, whether it be in H10 or I10...it's like I want
the formula to use L10 and if there is a date in I10, subtract that. But
if
there isn't a date in I10, I want it to check H10 and if there is a date
there, I want it to subtract that.

I want it to check certain previous cells until it finds one with a date
in
it and subtract that from L10.

"T. Valko" wrote:

=IF(AND(COUNT(H10),COUNT(I10)),I10-H10,"N/A")

You can reduce that to:

=IF(COUNT(H10,I10)=2,I10-H10,"N/A")

Ok, I'm confused as to where to get the *last* date to include in the
calculation. Can you reword your description?

--
Biff
Microsoft Excel MVP


"sarajane82" wrote in message
...
i am trying to develop a conditional formula. the spreadsheet is
separated
into sections, and in each 4-column section, there are 2 columns that
contain
dates. the 3rd contains a formula to subtract one from the other to
show
the
number of days in between.

the problem is that some of the original 2-columns do not contain
values.
i
have figured out how to make the formula in the 3rd column show "N/A"
if
one
of the columns has no value. the issue now is in any of the NEXT
4-column
sections, IF there is a date value in that row, i want it to go back to
the
last cell in that row that had a date in it and subtract THAT date.

for example: D5= 3/31/05, E5= 4/7/05, formula in F5 calculates "7".

If D10= "N/A" and E10= 4/14/05, F10 calcuates "N/A" (which is what i
want).

But then H10="N/A" and I10= 8/29/05. Instead of J10 caluclating "N/A"
(which is what my current formula,
=IF(AND(COUNT(H10),COUNT(I10)),I10-H10,"N/A"), produces, I want a
formula
that will see that since H10 is "N/A," it needs to go back and check
first
in
E10 and then in D10 and subtract the date from whichever of those it
picks
up
a date in first. So I want it to end up doing I10-E10, but I want to
make
it
conditional so I can make it consistent across the entire spreadsheet
to
account for times when there are "N/A's" in the date fields.

This is in Excel 2003