How to start week numbering (WEEKNUM) on a certain day?

[Simon Lloyd]

Hi all,
I have been trying to use WEEKNUM to determine week numbers for my
given dates, however my company starts their weekdays on a Sunday (no
problem as i use =IF($A1="","",WEEKNUM($A1,1)) ), the problem is that a
new week (week 2) starts on Sunday 4th January 2009, my company started
their first week on that Sunday, so its the begining of week 1!

I have tried manipulating this
=1+INT((A1-DATE(YEAR(A1+4-WEEKDAY(A1+6)),1,5)+WEEKDAY(DATE(YEAR(A1+4-WEEKDAY(A1+6)),1,3)))/7)
and can get it to work to a fashion for 09 but moving in to 2010 it
doesn't. I need the formula to work year on year, any ideas?

I will be adding the formulae to cells programatically.


--
Simon Lloyd

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com)

 

[Glenn]

Hi all,
I have been trying to use WEEKNUM to determine week numbers for my
given dates, however my company starts their weekdays on a Sunday (no
problem as i use =IF($A1="","",WEEKNUM($A1,1)) ), the problem is that a
new week (week 2) starts on Sunday 4th January 2009, my company started
their first week on that Sunday, so its the begining of week 1!

I have tried manipulating this
=1+INT((A1-DATE(YEAR(A1+4-WEEKDAY(A1+6)),1,5)+WEEKDAY(DATE(YEAR(A1+4-WEEKDAY(A1+6)),1,3)))/7)
and can get it to work to a fashion for 09 but moving in to 2010 it
doesn't. I need the formula to work year on year, any ideas?

I will be adding the formulae to cells programatically.

Does this work for you?

=WEEKNUM(A1-WEEKDAY(A1)+1)-1+(WEEKDAY(DATE(YEAR(A1-WEEKDAY(A1)+1),1,1))=1)

 

[Simon Lloyd]

Simon Lloyd wrote






Does this work for you

=WEEKNUM(A1-WEEKDAY(A1)+1)-1+(WEEKDAY(DATE(YEAR(A1-WEEKDAY(A1)+1),1,1))=1

--
Simon Lloy

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com

 

[Glenn]

Glenn worked perfect thanks!!!

You're welcome! By the way, this can be slightly simplified:

=WEEKNUM(A1-WEEKDAY(A1)+1)-(WEEKDAY(DATE(YEAR(A1-WEEKDAY(A1)+1),1,1))<>1)