Week Function

  • Thread starter Thread starter Michael
  • Start date Start date
M

Michael

Hi! I was wondering if anybody knew the Excel function to
get the number of the week from a date. For instance,
01/03/2004 would be Week 1, 01/09/2004 would be Week 2,
and so on.

That would really help.

Thanks!
 
Hi Michael!

Pick a system! You have four common options:

1. Week 1 starts 1-Jan and Week 2 starts on 8-Jan
Here, we use the formula:
=TRUNC(((A1-DATE(YEAR(A1),1,0))+6)/7)

2. Week 1 starts 1-Jan and week 2 starts on the following Sunday
Here we use the Analysis ToolPak WEEKNUM function with second argument
of 1.
=WEEKNUM(A1,1)

3. Week 1 starts 1-Jan and week 2 starts on the following Monday
Here we use the Analysis ToolPak WEEKNUM function with second argument
of 2.
=WEEKNUM(A1,2)

4. The ISO 8601: 2000 Method. Week 1 starts on the Monday of the week
with the first Thursday of the calendar year. Week 2 starts the
following Monday.
Here we use the formula:
=1+INT((A9-DATE(YEAR(A9+4-WEEKDAY(A9+6)),1,5)+WEEKDAY(DATE(YEAR(A9+4-W
EEKDAY(A9+6)),1,3)))/7)

The advantage of the ISO system is that all weeks, for all time have 7
days. With other methods you get odd weeks at the start and / or end
of the year. a disadvantage is that week 1 can start in late December
and week 53 can extend into January.

You two dates are rather unusual in that they are in week 1 and week 2
under all four systems.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Hi Norman,
4. The ISO 8601: 2000 Method. Week 1 starts on the Monday of the week
with the first Thursday of the calendar year. Week 2 starts the
following Monday.
Here we use the formula:
=1+INT((A9-DATE(YEAR(A9+4-WEEKDAY(A9+6)),1,5)+WEEKDAY(DATE(YEAR(A9+4-W
EEKDAY(A9+6)),1,3)))/7)
Click to expand...

FYI, Christophe Péjout posted a shorter alternative in the french XL ng a while
back:

=INT(MOD(INT((A9-2)/7)+0.6,52+5/28))+1

It's base on 28 recurring years similar calendar logic.
It has 2 shortcomings:

1.As any formula using MOD directly on the date number, it can't be compatible
with both calendars. This one is only valid with 1900 calendar : this is rather
serious.

2.Only valid until 2104, (Jan 28 2104 to be exact, but I'll be dead by then
:-) ) Just hoping MS will have extended its WEEKNUM() to allow for
ISO-weeknumbering by that time.

Again, only fyi. Regards,

Daniel M.
 
Hi Daniel!

Thanks for that. I actually recalled a shorter version but after a
short Google search I gave up on it.

I'd agree about seriousness of utility for 1900 date system only. We
must do something for the handicapped (aka Mac users <gdr>)

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Well, I know that you're waiting for a rejoinder...<g>

Let's just say that MacXL users are still using a *correct* date system,
one that doesn't throw in an extra day every century or two... They're
also the one more internally consistent: formatting 0.5 as a date
doesn't generate the 0th of January. They're also using the original XL
date system, of course. And do let's not forget that the Mac default
sytem doesn't just give up and go tango uniform when you ask it to
subtract a larger time from a smaller...
 
Hi JE!

They don't call me the office stirrer for nothing!

Good setting out of the pros and cons of 1904 dates system.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Back
Top