Weeks between dates

[Guest]

How can I find the number of weeks between two dates when they fall in
different years?

 

[Bob Phillips]

Simple way, subtract earlier date from later date and divide by 7. If you
want whole weeks, INT it or ROUND it

=(A2-A1)/7
=INT((A2-A1)/7)
=ROUND((A2-A1)/7,0)

 

[Guest]

Yah, I started there, but found that if one date is, say, on a Monday & the
other is on a Friday, this method does not give me the correct number of
weeks because of rounding down. I need something a little more exact. I
have had success with the WEEKNUM function when the two dates are in the same
year, but using dates in different years proves beyond my ken. Can you help?
For example, the number of weeks between 3-Oct-05 and 15-Oct-04.

b.

 

[Ron Rosenfeld]

Yah, I started there, but found that if one date is, say, on a Monday & the
other is on a Friday, this method does not give me the correct number of
weeks because of rounding down. I need something a little more exact. I
have had success with the WEEKNUM function when the two dates are in the same
year, but using dates in different years proves beyond my ken. Can you help?
For example, the number of weeks between 3-Oct-05 and 15-Oct-04.

How do you want to handle fractional weeks?

If you just want to represent it as a fraction, then:

=(A2-A1)/7 will do that. Just format it how you wish.

For your dates above: 50.42857143 weeks.


If you want to represent as, for example, ww.d, then:

If you have the Analysis tool pak:

=dollarfr((F1-F2)/7,7)

If you do not have the ATP installed then:

=MOD((F1-F2)/7,1)*7/10+INT((F1-F2)/7)

Or, as a text string:

=INT((F1-F2)/7)&" w "&TEXT(MOD((F1-F2)/7,1)*7,"0 \d")

51 w 1 d




--ron