Formula to calculate the difference in weeks between two dates

[IGG]

Hi,

I' am hoping you can help with what might seem a basic query.

I have a row of dates in one column for which I want to find the difference
between the earlist and latest date i.e. =max(A1:A10)-=Min(A1:A10); however I
cant get the cell to calculate the number of weeks difference as apposed to
the number of days.

Secondly I need to apply the same rule to the column again; but only
concentrating on those dates after a date of my choice i.e. The number of
weeks between the 31/03/08 and the latest date in the column.

 

[macropod]

Hi IGG,

Is there a reason you can't simply divide the number of days by 7?
=(MAX(A1:A10)-MIN(A1:A10))/7

Cheers

 

[Sandy Mann]

Number of weeks:

=INT((MAX(A1:A10)-MIN(A1:A10))/7)

or if you want the odd days as well:

=INT((MAX(A1:A10)-MIN(A1:A10))/7)&" Weeks & "
&MOD((MAX(A1:A10)-MIN(A1:A10)),7)&" Days"

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Mike H]

Try this

=INT((MAX(A1:A10)-MIN(A1:A10))/7)&" Weeks
"&MOD(MAX(A1:A10)-MIN(A1:A10),7)&" Days"

For the second part of your question put the date 31/3/2008 in a cell (say) B1
and use

=INT((B1-MAX(A1:A10))/7)&" Weeks "&MOD(B1-MAX(A1:A10),7)&" Days"

Mike