=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