Week Number
Gary
I know various formulas for calculating Week Numbers, but none of them
work for me properly.
My weeks always start on a Saturday, and don't appear too follow
either ISO convention or MS WEEKNUM.
This Year (2000) started on Saturday 1st January, which (for me) is
the first day of week 1.
This year ends on Friday 29th December 2000, which is the last day of
week 52.
Week 1 in 2001 starts on Saturday 30th December 2000.
Week 52 in 2001 ends on Friday 28th December 2000.
Week 1 in 2002 starts on Saturday 29th December 2001
Week 53 in 2002 ends on Friday 3rd January 2003.
The closest I have got to a working formula so far is:
=RIGHT(YEAR(A8),2)&TEXT(TRUNC(((A8-DATE(YEAR(A8),1,1))/7))+1+IF(WEEKDAY(DATE(YEAR(A8),1,1))>WEEKDAY(A8),1,0)+IF(WEEKDAY(A8)=7,1,0)-1,"00")
adapted from one of Chip's formulas. This doesn't work.
Can anyone help?
TIA
Gary
_____________________________________________________
My views, not Auntie's - but Auntie's dates not mine!
Gary
I adapted John Green's ISOWeekNum function from Chip's excellent
web-site:
http://www.cpearson.com/excel/DateTimeVBA.htm
Gary
______________________
My views, not Auntie's
On Wed, 09 Feb 2000 12:39:39 +0000, Gary <gary....@bbc.co.uk>
wrote:
Harlan Grove
occasionally have to go out to 53 bceause every 5 to 6
years you've built up one full week more than 5 or 6 times
52, what's the criteria for having a week 53?
* Sent from AltaVista http://www.altavista.com Where you can also find related Web Pages, Images, Audios, Videos, News, and Shopping. Smart is Beautiful
George Simms
Problems Returning The Correct Week Number.
As you will know the weeknum() function returns week 1 from the date
1/1/#### . Week 2 starting the following Sunday .
Using the function on the following dates returns , Friday 31/12/1999 as
53 : Saturday 01/01/2000 as 1 : Sunday 02/01/2000 as 2. ( Date format :
dd/mm/yyyy).
This is completely wrong, by the rules of the 'International Standard ISO
8601' for dates & time notation.
http://www.cl.cam.ac.uk/~mgk25/iso-time.html
Another example is 09/1/1977 weeknum() returns week 3, when this is still
ISO week 1.
Calculating Week One Using the ISO Standard .
The above week numbers should the be :
Friday 31/12/1999 w52 : Saturday 1/1/2000 w52 : Sunday 2/1/2000
w52 Week 1 beginning 3/1/2000.
Week number 1 is the first week of a new year that contains a Thursday .
The 'week' beginning on Monday and ending Sunday.
If the 1st of January is a Friday, Saturday. or Sunday then that week
number could be 52 or 53 depending on the day that started the previous
year.
In 1998 Jan 1st was a Thursday and that week was week 1, with the week
beginning 29 Dec 1997.
In 1999 Jan 1st was a Friday and that made it week 53. With week 1
beginning
on the 3rd of Jan 2000.
The only way to return the correct week number ( I think ) is by a
calculation, as the formula by Laurent Longre (very neat) :
=INT((A1-SUM(MOD(DATE(YEAR(A1-MOD(A1-2,7)+3),1,2),{1E+99,7})*{1,-1})+5)/7)
Or this.
=INT((A1-(DATE(YEAR(A1+(MOD(8-WEEKDAY(A1),7)-3)),1,1))-3+MOD(WEEKDAY(DATE(Y
EAR(A1+(MOD(8-WEEKDAY(A1),7)-3)),1,1))+1,7))/7)+1
There is a function in a free add-in by Laurent ISO.WEEKNUM(date)
that I assume uses the above formula, and, doesn't return an error for
1/1/1900 (week
52) as with the formulae.
http://longre.free.fr/english
(FWIW. There are 32 other very useful functions in the add-in)
VBA Problems.
There is also a bug using VBA . I assumed I had solved the problem with
this simple function:
Function ISO_WeekNumber(WDate As Date) As Integer
Application.Volatile
ISO_WeekNumber=Format(WDate,"WW",vbMonday,vbFirstFourdays)
End Function
But testing this, it gave the wrong results
for some dates, 1907,1919,1991,1992, 2003 & 2007........
A similar error was found with:
DatePart("ww",#1/1/92#,vbMonday,vbFirstFourDays)
I haven't tested it in 2000, it might have been fixed. It has something to
do with vbFirstFourDays.
Chip Pearson has some code by John Green the returns the correct ISO week
number. http://www.cpearson.com/excel/datetime.htm
It's at the end of this page.
All the Best
George
Newcastle upon Tyne
England.
Harlan Grove wrote in message.....
> Idly currious - since ISO and Excel week numbers
> occasionally have to go out to 53 because every 5 to 6
Gary
The 53 week years occur with the same frequency as in ISO, just out of
sync (as far as I can tell - no seems to know for certain!). Probably
something to do with how many furlongs we are from the Greenwich
meridian when there is an "r" in the month <g>.
John's adapted function works for me up to 2004 which will do for
now!!
All the best
Gary
______________________
My views, not Auntie's
On Wed, 09 Feb 2000 10:10:38 -0800, Harlan Grove
<hrlngrv...@aol.com.invalid> wrote:
>Idly currious - since ISO and Excel week numbers
>occasionally have to go out to 53 bceause every 5 to 6
>years you've built up one full week more than 5 or 6 times
>52, what's the criteria for having a week 53?
>
>
George Simms
Apologies to Harlan for the spelling error. and to you for not reading your
question first .
This formula is out of sync with the ISO week num by two days week starts
Sat and not Mon.
Test it out, I have only tested it from 1/1/1999 to 1/1/2008
=INT((A2-(DATE(YEAR(A2+(MOD(6-WEEKDAY(A2),7)-1)),1,1))-1+MOD(WEEKDAY(DATE(Y
EAR(A2+(MOD(6-WEEKDAY(A2),7)-1)),1,1))+1,7))/7)+1
All the Best
George
Newcastle upon Tyne
England.
Gary wrote in message ...
Gary
Thanks for the below formula, unfortunately the BBC in its' wisdom has
decreed that 2002 has 53 weeks in it, so the formula falls over there.
(This will mean that financially November had 5 weeks in it that
year).
I think the best thing I can do is to try and find out who decrees
these things here and get them to accept ISO standards!!
Thanks as ever..
Gary
___________________________________
My views, but Auntie's week numbers
Nick Hodge
That's why the licence fee's going up <G>
Sorry, *very* UK specific
--
HTH
Nick Hodge
Southampton, England
nick_...@lineone.net
Gary <gary....@bbc.co.uk> wrote in message
news:UJCiOHm4ZcTu1C...@4ax.com...
Harlan Grove
<gary....@bbc.co.uk> wrote:
..
> . . . unfortunately the BBC in its' wisdom has decreed
>that 2002 has 53 weeks in it, so the formula falls over
>there. (This will mean that financially November had 5
>weeks in it that year).
>
>I think the best thing I can do is to try and find out who
>decrees these things here and get them to accept ISO
>standards!!
Actually, if 2002 has 53 weeks and the BBC's operating
years always start on Saturday, then the BBC is following
ISO weeknumber logic but using Saturday rather than Monday
as the trigger for new weeknumbers. That is, 31 Dec 2002 is
a Tuesday, so there are four days from Saturday 28 Dec 2002
to 31 Dec 2002, so most of a full week falls on and after
the last Saturday of the year.
The number of days between Monday 28 Dec 1998 and Saturday
28 Dec 2002 is 1461. Monday 28 Dec 1998 was the most recent
53 week ISO year also falling midway between leap years. If
you have a formula that gives ISO week numbers (like the
ISO.WEEKNUM function in Laurent Longre's MOREFUNC.XLL), you
should be able to get BBC week numbers using the formula
=ISO.WEEKNUM(ActualDate-1461)
Note: this will fail around 2100 since 2100 is _NOT_ a leap
year, unlike 2000, but it's our turn to pass on a Y2K
legacy to those who come after us. ;-)
George Simms
Using Harlan's 1461 with Laurents formula. ( if you can't use the add-in).
This appears to work :
=INT(((A2-1461)-SUM(MOD(DATE(YEAR(A2-MOD(A2,7)+3),1,2)-1461,{1E+99,7})*{1,-
1})+5)/7)
--
All the Best
George
Newcastle upon Tyne
England.
Gary <gary....@bbc.co.uk> wrote in message
news:UJCiOHm4ZcTu1C...@4ax.com...
> Hello George
>
> Thanks for the below formula, unfortunately the BBC in its' wisdom has
> decreed that 2002 has 53 weeks in it, so the formula falls over there.
>
> (This will mean that financially November had 5 weeks in it that
> year).
>
> I think the best thing I can do is to try and find out who decrees
> these things here and get them to accept ISO standards!!
>
> Thanks as ever..
>
> Gary
Gary
bright golden haze on the meadow!
I now need to calculate what Financial Month any given date is in,
which even I should be able to with the superlative help I have
already received!
Thanks again
Gary
______________________
My views, not Auntie's
(although I'm sure she'd agree with me this time!)