Works perfectly. Thanks Kassie. Thanks JW.

"kassie" wrote:

Your problem is the placement of your last argument. If you look at the
structure of your formula, you will notice that this sum will only be
calculated if Date 1 and Date 2 are both 0.

=IF(AND(A14="",B14=""),"",IF(AND(B14="",A140),(NO W()-A14)/7,IF(AND(A140,B140),IF(A14=B14,0.14,(B14-A14)/7))))

should do the trick. I take it you want to be able to do calculations based
on the results, hence I treated everything as numbers, rather than text?

--
Hth

Kassie Kasselman
Change xxx to hotmail


"dmasch" wrote:

I am writing a formula to calculate the number of weeks between two date
fields. Easy enough, =(Date2-Date1)/7. Problem is that not all date fields
have a value and sometimes the two date fields are the same. Here are the
possibilities and the desired result:
Date1 Date2 Result
Null Null Null
x y (y-x)/7
x x 0.14
x Null (Today()-x)/7

Here is what I have right now:

=IF(AND(Date1="",Date2=""),"",IF(AND(Date10,Date2 0),IF(Date1=Date2,"0.14",(Date2-Date1)/7),IF(AND(Date2="",Date10),((TODAY()-Date1)/7))))

Everything seems to work except for when Date1 has a value and Date2 does
not. I get #VALUE! in that case. I tried messing with the perens around the
today() part of the formula but can't seem to get it working.

I am using Excel 2002. Many thanks.