Hi,

I had a file with different date on it.
If I need to consolidate them into week.
example
4 Jan to 10 Jan is call week 1
11 Jan to 17 jan is week 2
is it possible to calculator 22 Mar is in which week(week 12)

On Jan 22, 7:56*am, Sky wrote:
Hi,

I had a file with different date on it.
If I need to consolidate them into week.
example
4 Jan to 10 Jan is call week 1
11 Jan to 17 jan is week 2
is it possible to calculator 22 Mar is in which week(week 12)

Assuming your date is in B4.

Try this to get the standard ISO week number : =INT((B4-DATE(YEAR(B4-
WEEKDAY(B4-1)+4),1,3)+WEEKDAY(DATE(YEAR(B4-WEEKDAY(B4-1)+4),1,3))+5)/
7)

=WEEKNUM(A1,2)-1
--
David Biddulph

"Sky" wrote in message
...
Hi,

I had a file with different date on it.
If I need to consolidate them into week.
example
4 Jan to 10 Jan is call week 1
11 Jan to 17 jan is week 2
is it possible to calculator 22 Mar is in which week(week 12)

If A1 contains 22/3/2010 as a real Excel date then
=WEEKNUM(A1,1)-1
returns 12.
Analysis Toolpak is needed!

--
Regards!
Stefi



€žSky€� ezt Ã*rta:

Hi,

I had a file with different date on it.
If I need to consolidate them into week.
example
4 Jan to 10 Jan is call week 1
11 Jan to 17 jan is week 2
is it possible to calculator 22 Mar is in which week(week 12)

Hi Stefi,

Cannot get it.
May I know what is returns 12 and Analysis Toolpak is needed mean?

"Stefi" wrote:

If A1 contains 22/3/2010 as a real Excel date then
=WEEKNUM(A1,1)-1
returns 12.
Analysis Toolpak is needed!

--
Regards!
Stefi



€žSky€� ezt Ã*rta:

Hi,

I had a file with different date on it.
If I need to consolidate them into week.
example
4 Jan to 10 Jan is call week 1
11 Jan to 17 jan is week 2
is it possible to calculator 22 Mar is in which week(week 12)

"Returns 12" means that the result of the calculation is 12. Analysis
Tookpak is an add-on that must be loaded before the function specified will
work. Go to ToolsAddons to load it. You only have to do it once.

Regards,
Fred

"Sky" wrote in message
...
Hi Stefi,

Cannot get it.
May I know what is returns 12 and Analysis Toolpak is needed mean?

"Stefi" wrote:

If A1 contains 22/3/2010 as a real Excel date then
=WEEKNUM(A1,1)-1
returns 12.
Analysis Toolpak is needed!

--
Regards!
Stefi



€žSky€� ezt Ã*rta:

Hi,

I had a file with different date on it.
If I need to consolidate them into week.
example
4 Jan to 10 Jan is call week 1
11 Jan to 17 jan is week 2
is it possible to calculator 22 Mar is in which week(week 12)

Doesn't that change on a Sunday, rather than changing on a Monday as the OP
requested [assuming that the OP's dates are this year]?

Shouldn't it be =WEEKNUM(A1,2)-1 ?
--
David Biddulph

"Stefi" wrote in message
...
If A1 contains 22/3/2010 as a real Excel date then
=WEEKNUM(A1,1)-1
returns 12.
Analysis Toolpak is needed!

--
Regards!
Stefi



"Sky" ezt írta:

Hi,

I had a file with different date on it.
If I need to consolidate them into week.
example
4 Jan to 10 Jan is call week 1
11 Jan to 17 jan is week 2
is it possible to calculator 22 Mar is in which week(week 12)

Hi David,

Cannot get.
Could you send me an email with the file attach.


"David Biddulph" wrote:

=WEEKNUM(A1,2)-1
--
David Biddulph

"Sky" wrote in message
...
Hi,

I had a file with different date on it.
If I need to consolidate them into week.
example
4 Jan to 10 Jan is call week 1
11 Jan to 17 jan is week 2
is it possible to calculator 22 Mar is in which week(week 12)


.