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.

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")
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.

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.

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.