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.

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
--
macropod
[MVP - Microsoft Word]
-------------------------

"IGG" wrote in message ...
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.

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


Replace @mailinator.com with @tiscali.co.uk


"IGG" wrote in message
...
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.

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
"IGG" wrote:

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.