Hi,
I've looked at dozens of questions relating to week numbers on this board (and on others) and found nothing that covers what I need. So I'm hopefully not asking a question that's already been answered.
We run reports that need to align to our company's working week that runs from Saturday to Friday.
As many of you will know, Excel's Weeknum function does not allow for a week begining on a Saturday, so I can't use that.
To further complicate things, Week 1 starts on the Saturday <= Jan 1st and week 2 begins on the first Satuday after Jan 1st.
So for 2010 week 1 began on 26/12/2009 and week 2 on 02/01/2010 (dd/mm/yyyy format).
In 2011 week 1 begins on 01/01/2011 and week 2 on 08/01/2011.
I've managed to come up with this formula that works well enough for us to use
"=TRUNC(((A3-DATE(YEAR(A3),1,1))/7))+1+IF(WEEKDAY(DATE(YEAR(A3),1,1))>WEEKDAY(A3),1,0)+IF(WEEKDAY(A3,1)=7,1,0)+IF(WEEKDAY(DATE(YEAR(A3),1,1),1)=7,-1,0)".
It doesn't return the 'correct' week number for the days 26/12/2009 to 31/12/2009, for example, it returns week 53 for these. That's not really a problem for us as we shut down between Christmas and the New Year, so there's nothing to report on.
I've tested it up to 2020 and it does what I need for the weeks that we are actually working.
Now after all that, the question is does anyone know of a better way of achieving what we need?
Ideally it would be nice if it would calculate the correct week number for dates such as 26/12/2009 to 31/12/2009, that we see as week 1 for 2010.
Thank you for any help you can provide.
I've looked at dozens of questions relating to week numbers on this board (and on others) and found nothing that covers what I need. So I'm hopefully not asking a question that's already been answered.
We run reports that need to align to our company's working week that runs from Saturday to Friday.
As many of you will know, Excel's Weeknum function does not allow for a week begining on a Saturday, so I can't use that.
To further complicate things, Week 1 starts on the Saturday <= Jan 1st and week 2 begins on the first Satuday after Jan 1st.
So for 2010 week 1 began on 26/12/2009 and week 2 on 02/01/2010 (dd/mm/yyyy format).
In 2011 week 1 begins on 01/01/2011 and week 2 on 08/01/2011.
I've managed to come up with this formula that works well enough for us to use
"=TRUNC(((A3-DATE(YEAR(A3),1,1))/7))+1+IF(WEEKDAY(DATE(YEAR(A3),1,1))>WEEKDAY(A3),1,0)+IF(WEEKDAY(A3,1)=7,1,0)+IF(WEEKDAY(DATE(YEAR(A3),1,1),1)=7,-1,0)".
It doesn't return the 'correct' week number for the days 26/12/2009 to 31/12/2009, for example, it returns week 53 for these. That's not really a problem for us as we shut down between Christmas and the New Year, so there's nothing to report on.
I've tested it up to 2020 and it does what I need for the weeks that we are actually working.
Now after all that, the question is does anyone know of a better way of achieving what we need?
Ideally it would be nice if it would calculate the correct week number for dates such as 26/12/2009 to 31/12/2009, that we see as week 1 for 2010.
Thank you for any help you can provide.
