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

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)
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=120542

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

Simon Lloyd wrote:
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)

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

Glenn worked perfect thanks!!!Glenn;434348 Wrote:
Simon Lloyd wrote:
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

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com)
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=120542

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

Glenn;434348 Wrote:
Simon Lloyd wrote:
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 wrote:
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)