Weeknum

  • Thread starter Thread starter Peter
  • Start date Start date
P

Peter

I wish the 1st of april to be week 1 using the weeknum function.

If this is not possible, can you give me a formula that is similar to the
weeknum function that will allow the 1st of April to be week 1

thanks

Peter
 
Hi Peter!

Do you want to exactly replicate WEEKNUM with option 1; Week 1 starts
1-Apr with Week 2 commencing the following Sunday?

Or perhaps option 2 where week 2 starts the following Monday?

Or do you want Week 1 to start 1-Apr with Week 2 starting on April 8.

--
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.
 
If A1 = 4/01/04 and cell B1 contains =(WEEKNUM(A1,2) >> Value is
14
So enter:
=(WEEKNUM(A1,2)-13) and copy down...
HTH
 
Norman,

I want week 1 to start 1-Apr-2004 and
Week 52 to be last week in March 2005

I have used weeknum(a1,1)-13 as JMay suggested but when I come to 1-Jan, I
end up with -12 instead of 41

thanks

Peter
 
Hi Peter!

You haven't answered my question about week numbering system. When
does week 2 start?

If Week 2 starts 8-Apr, then the formula could be:

=IF(MONTH(A1)<4,INT((A1-DATE(YEAR(A1)-1,3,31))/7)+1,INT((A1-DATE(YEAR(
A1),3,31))/7)+1)

You'll see that dates before April require the base date of the
preceding year's April. Otherwise, this year's April is the base date
for week.

But this system will always give you a week 53 because 365/7 gives 52
Modulus 1 and 366/7 gives 52 Modulus 2. You'll not avoid that problem
with any of the week numbering algorithms that you come up with.
Indeed with some Leap Years, 31-Dec can fall into week 54! (e.g.

=WEEKNUM("31-Dec-2028",1)
and
=WEEKNUM("31-Dec-2012",2)

--
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.
 
Thanks Norman, this has achieved what I set out for, I will just have to
allow for the 53rd week.

Peter
 
Hi Peter!

Thanks for thanks. Glad we hit on a solution even if a little by
accident!

--
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