Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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) |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
WEEKNUM change start of week to a monday | Excel Discussion (Misc queries) | |||
WEEKNUM calculations for week stating with Tuesday | Excel Worksheet Functions | |||
WEEKNUM to start on another day | Excel Worksheet Functions | |||
start/end dates of a week given just the weeknum() value | Excel Discussion (Misc queries) | |||
weeknum function says jan1=week1, mod to first 4 day week needed | Excel Discussion (Misc queries) |