Formula for # weeks between 2 dates

[dmasch]

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(Date1>0,Date2>0),IF(Date1=Date2,"0.14",(Date2-Date1)/7),IF(AND(Date2="",Date1>0),((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.

 

[JW]

This work for you? Assuming Date1 is in G7 and Date2 is in H7.
=TEXT(IF(AND(G7="",H7=""),"",IF(AND(G7<=0,H7<=0),"0",IF(H7=G7,"0.14",IF(H7<>"",
(H7-G7)/7,(TODAY()-G7)/7)))),"0.00")

You could also use DATEDIF to workout the weeks between the dates.
=TEXT(IF(AND(G7="",H7=""),"",IF(AND(G7<=0,H7<=0),"0",IF(H7=G7,"0.14",IF(H7<>"",DATEDIF(G7,H7,"d")/
7,DATEDIF(TODAY(),G7,"d")/7)))),"0.00")

 

[kassie]

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="",A14>0),(NOW()-A14)/7,IF(AND(A14>0,B14>0),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?