Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Week Number using VBA in Excel
Hi Can you please help to get this script run in excel. as I am finding
difficulties in running. Public Function ISOWeekNum(AnyDate As Date, _ Optional WhichFormat As Variant) As Integer ' ' WhichFormat: missing or < 2 then returns week number, ' = 2 then YYWW ' Dim ThisYear As Integer Dim PreviousYearStart As Date Dim ThisYearStart As Date Dim NextYearStart As Date Dim YearNum As Integer ThisYear = Year(AnyDate) ThisYearStart = YearStart(ThisYear) PreviousYearStart = YearStart(ThisYear - 1) NextYearStart = YearStart(ThisYear + 1) Select Case AnyDate Case Is = NextYearStart ISOWeekNum = (AnyDate - NextYearStart) \ 7 + 1 YearNum = Year(AnyDate) + 1 Case Is < ThisYearStart ISOWeekNum = (AnyDate - PreviousYearStart) \ 7 + 1 YearNum = Year(AnyDate) - 1 Case Else ISOWeekNum = (AnyDate - ThisYearStart) \ 7 + 1 YearNum = Year(AnyDate) End Select If IsMissing(WhichFormat) Then Exit Function End If If WhichFormat = 2 Then ISOWeekNum = CInt(Format(Right(YearNum, 2), "00") & _ Format(ISOWeekNum, "00")) End If End Function |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Week Number using VBA in Excel
Hi
Use the ISO function here http://www.rondebruin.nl/isodate.htm I use this function as a example on my Personal.xls(b) page http://www.rondebruin.nl/personal.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Kam" wrote in message ... Hi Can you please help to get this script run in excel. as I am finding difficulties in running. Public Function ISOWeekNum(AnyDate As Date, _ Optional WhichFormat As Variant) As Integer ' ' WhichFormat: missing or < 2 then returns week number, ' = 2 then YYWW ' Dim ThisYear As Integer Dim PreviousYearStart As Date Dim ThisYearStart As Date Dim NextYearStart As Date Dim YearNum As Integer ThisYear = Year(AnyDate) ThisYearStart = YearStart(ThisYear) PreviousYearStart = YearStart(ThisYear - 1) NextYearStart = YearStart(ThisYear + 1) Select Case AnyDate Case Is = NextYearStart ISOWeekNum = (AnyDate - NextYearStart) \ 7 + 1 YearNum = Year(AnyDate) + 1 Case Is < ThisYearStart ISOWeekNum = (AnyDate - PreviousYearStart) \ 7 + 1 YearNum = Year(AnyDate) - 1 Case Else ISOWeekNum = (AnyDate - ThisYearStart) \ 7 + 1 YearNum = Year(AnyDate) End Select If IsMissing(WhichFormat) Then Exit Function End If If WhichFormat = 2 Then ISOWeekNum = CInt(Format(Right(YearNum, 2), "00") & _ Format(ISOWeekNum, "00")) End If End Function |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Week Number using VBA in Excel
And those difficulties you are having are what? The more details you give
us, the better able we are to figure out how to help you. It looks like you copied John Green's ISOWeekNum function correctly... did you also copy the YearStart function which the ISOWeekNum function depends on? Rick "Kam" wrote in message ... Hi Can you please help to get this script run in excel. as I am finding difficulties in running. Public Function ISOWeekNum(AnyDate As Date, _ Optional WhichFormat As Variant) As Integer ' ' WhichFormat: missing or < 2 then returns week number, ' = 2 then YYWW ' Dim ThisYear As Integer Dim PreviousYearStart As Date Dim ThisYearStart As Date Dim NextYearStart As Date Dim YearNum As Integer ThisYear = Year(AnyDate) ThisYearStart = YearStart(ThisYear) PreviousYearStart = YearStart(ThisYear - 1) NextYearStart = YearStart(ThisYear + 1) Select Case AnyDate Case Is = NextYearStart ISOWeekNum = (AnyDate - NextYearStart) \ 7 + 1 YearNum = Year(AnyDate) + 1 Case Is < ThisYearStart ISOWeekNum = (AnyDate - PreviousYearStart) \ 7 + 1 YearNum = Year(AnyDate) - 1 Case Else ISOWeekNum = (AnyDate - ThisYearStart) \ 7 + 1 YearNum = Year(AnyDate) End Select If IsMissing(WhichFormat) Then Exit Function End If If WhichFormat = 2 Then ISOWeekNum = CInt(Format(Right(YearNum, 2), "00") & _ Format(ISOWeekNum, "00")) End If End Function |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Week Number using VBA in Excel
On Sat, 4 Aug 2007 08:00:01 -0700, Kam wrote:
Hi Can you please help to get this script run in excel. as I am finding difficulties in running. Public Function ISOWeekNum(AnyDate As Date, _ Optional WhichFormat As Variant) As Integer ' ' WhichFormat: missing or < 2 then returns week number, ' = 2 then YYWW ' Dim ThisYear As Integer Dim PreviousYearStart As Date Dim ThisYearStart As Date Dim NextYearStart As Date Dim YearNum As Integer ThisYear = Year(AnyDate) ThisYearStart = YearStart(ThisYear) PreviousYearStart = YearStart(ThisYear - 1) NextYearStart = YearStart(ThisYear + 1) Select Case AnyDate Case Is = NextYearStart ISOWeekNum = (AnyDate - NextYearStart) \ 7 + 1 YearNum = Year(AnyDate) + 1 Case Is < ThisYearStart ISOWeekNum = (AnyDate - PreviousYearStart) \ 7 + 1 YearNum = Year(AnyDate) - 1 Case Else ISOWeekNum = (AnyDate - ThisYearStart) \ 7 + 1 YearNum = Year(AnyDate) End Select If IsMissing(WhichFormat) Then Exit Function End If If WhichFormat = 2 Then ISOWeekNum = CInt(Format(Right(YearNum, 2), "00") & _ Format(ISOWeekNum, "00")) End If End Function What problems are you having? There are simpler algorithms. For example, to just get the ISOWeeknumber: ================================== Function ISOWeeknum(dt As Date) As Integer ISOWeeknum = DatePart("ww", dt, vbMonday, vbFirstFourDays) If ISOWeeknum 52 Then If DatePart("ww", dt + 7, vbMonday, vbFirstFourDays) = 2 Then ISOWeeknum = 1 End If End If End Function ==================================== --ron |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Week Number using VBA in Excel
"Ron Rosenfeld" wrote in message ... On Sat, 4 Aug 2007 08:00:01 -0700, Kam wrote: Hi Can you please help to get this script run in excel. as I am finding difficulties in running. Public Function ISOWeekNum(AnyDate As Date, _ Optional WhichFormat As Variant) As Integer ' ' WhichFormat: missing or < 2 then returns week number, ' = 2 then YYWW ' Dim ThisYear As Integer Dim PreviousYearStart As Date Dim ThisYearStart As Date Dim NextYearStart As Date Dim YearNum As Integer ThisYear = Year(AnyDate) ThisYearStart = YearStart(ThisYear) PreviousYearStart = YearStart(ThisYear - 1) NextYearStart = YearStart(ThisYear + 1) Select Case AnyDate Case Is = NextYearStart ISOWeekNum = (AnyDate - NextYearStart) \ 7 + 1 YearNum = Year(AnyDate) + 1 Case Is < ThisYearStart ISOWeekNum = (AnyDate - PreviousYearStart) \ 7 + 1 YearNum = Year(AnyDate) - 1 Case Else ISOWeekNum = (AnyDate - ThisYearStart) \ 7 + 1 YearNum = Year(AnyDate) End Select If IsMissing(WhichFormat) Then Exit Function End If If WhichFormat = 2 Then ISOWeekNum = CInt(Format(Right(YearNum, 2), "00") & _ Format(ISOWeekNum, "00")) End If End Function What problems are you having? There are simpler algorithms. For example, to just get the ISOWeeknumber: ================================== Function ISOWeeknum(dt As Date) As Integer ISOWeeknum = DatePart("ww", dt, vbMonday, vbFirstFourDays) If ISOWeeknum 52 Then If DatePart("ww", dt + 7, vbMonday, vbFirstFourDays) = 2 Then ISOWeeknum = 1 End If End If End Function ==================================== --ron Hi Ron! Your ISOWeeknum(dt As Date) has the advantage of sidestepping the bug in Datepart in a clear way. But some of the errors remain. Sun 2 jan 2101 still gives week 53, which should be 52 like Sat 1 jan 2101. A bit into the future, I'll admit. Staying with Datepart there is this: Function ISOWknum(d As Date) '25/12/1899-26/12/9999 ISOWknum = DatePart("ww", ((d + 5) \ 7) * 7, 2, 2) End Function It looks at the Saturday of the same week. Otherwise I'm quite proud of this Function WkIso(d) '..1/1/100-31/12/9999.. WkIso = ((((d + 692501) \ 7 Mod 20871) * 28 + 4383) Mod 146096 Mod 1461) \ 28 + 1 End Function It is very fast, doesn't need datefunctions. Hans. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Week Number using VBA in Excel
On Sun, 5 Aug 2007 13:32:59 +0200, "Hans Terkelsen" <dk wrote:
"Ron Rosenfeld" wrote in message ... On Sat, 4 Aug 2007 08:00:01 -0700, Kam wrote: Hi Can you please help to get this script run in excel. as I am finding difficulties in running. Public Function ISOWeekNum(AnyDate As Date, _ Optional WhichFormat As Variant) As Integer ' ' WhichFormat: missing or < 2 then returns week number, ' = 2 then YYWW ' Dim ThisYear As Integer Dim PreviousYearStart As Date Dim ThisYearStart As Date Dim NextYearStart As Date Dim YearNum As Integer ThisYear = Year(AnyDate) ThisYearStart = YearStart(ThisYear) PreviousYearStart = YearStart(ThisYear - 1) NextYearStart = YearStart(ThisYear + 1) Select Case AnyDate Case Is = NextYearStart ISOWeekNum = (AnyDate - NextYearStart) \ 7 + 1 YearNum = Year(AnyDate) + 1 Case Is < ThisYearStart ISOWeekNum = (AnyDate - PreviousYearStart) \ 7 + 1 YearNum = Year(AnyDate) - 1 Case Else ISOWeekNum = (AnyDate - ThisYearStart) \ 7 + 1 YearNum = Year(AnyDate) End Select If IsMissing(WhichFormat) Then Exit Function End If If WhichFormat = 2 Then ISOWeekNum = CInt(Format(Right(YearNum, 2), "00") & _ Format(ISOWeekNum, "00")) End If End Function What problems are you having? There are simpler algorithms. For example, to just get the ISOWeeknumber: ================================== Function ISOWeeknum(dt As Date) As Integer ISOWeeknum = DatePart("ww", dt, vbMonday, vbFirstFourDays) If ISOWeeknum 52 Then If DatePart("ww", dt + 7, vbMonday, vbFirstFourDays) = 2 Then ISOWeeknum = 1 End If End If End Function ==================================== --ron Hi Ron! Your ISOWeeknum(dt As Date) has the advantage of sidestepping the bug in Datepart in a clear way. But some of the errors remain. Sun 2 jan 2101 still gives week 53, which should be 52 like Sat 1 jan 2101. A bit into the future, I'll admit. Staying with Datepart there is this: Function ISOWknum(d As Date) '25/12/1899-26/12/9999 ISOWknum = DatePart("ww", ((d + 5) \ 7) * 7, 2, 2) End Function It looks at the Saturday of the same week. Otherwise I'm quite proud of this Function WkIso(d) '..1/1/100-31/12/9999.. WkIso = ((((d + 692501) \ 7 Mod 20871) * 28 + 4383) Mod 146096 Mod 1461) \ 28 + 1 End Function It is very fast, doesn't need datefunctions. Hans. Very good Hans! I was not aware of that BUG in DatePart. (And, apparently, neither was Microsoft, as the only BUG they mention is the one for Mondays). I like both of your workarounds. The one I came up with is not as fast as yours, but it does clarify the bugs, I think. I haven't tested it yet, though: ======================================== Function ISOWeeknum(dt As Date) As Integer ISOWeeknum = DatePart("ww", dt + (Weekday(dt, vbMonday) < 1), _ vbMonday, vbFirstFourDays) If DatePart("ww", dt + 7, vbMonday, vbFirstFourDays) = 2 _ Then ISOWeeknum = 1 End Function =========================================== --ron |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Week Number using VBA in Excel
"Ron Rosenfeld" wrote in message ... On Sun, 5 Aug 2007 13:32:59 +0200, "Hans Terkelsen" <dk wrote: "Ron Rosenfeld" wrote in message ... On Sat, 4 Aug 2007 08:00:01 -0700, Kam wrote: Hi Can you please help to get this script run in excel. as I am finding difficulties in running. Public Function ISOWeekNum(AnyDate As Date, _ Optional WhichFormat As Variant) As Integer ' ' WhichFormat: missing or < 2 then returns week number, ' = 2 then YYWW ' Dim ThisYear As Integer Dim PreviousYearStart As Date Dim ThisYearStart As Date Dim NextYearStart As Date Dim YearNum As Integer ThisYear = Year(AnyDate) ThisYearStart = YearStart(ThisYear) PreviousYearStart = YearStart(ThisYear - 1) NextYearStart = YearStart(ThisYear + 1) Select Case AnyDate Case Is = NextYearStart ISOWeekNum = (AnyDate - NextYearStart) \ 7 + 1 YearNum = Year(AnyDate) + 1 Case Is < ThisYearStart ISOWeekNum = (AnyDate - PreviousYearStart) \ 7 + 1 YearNum = Year(AnyDate) - 1 Case Else ISOWeekNum = (AnyDate - ThisYearStart) \ 7 + 1 YearNum = Year(AnyDate) End Select If IsMissing(WhichFormat) Then Exit Function End If If WhichFormat = 2 Then ISOWeekNum = CInt(Format(Right(YearNum, 2), "00") & _ Format(ISOWeekNum, "00")) End If End Function What problems are you having? There are simpler algorithms. For example, to just get the ISOWeeknumber: ================================== Function ISOWeeknum(dt As Date) As Integer ISOWeeknum = DatePart("ww", dt, vbMonday, vbFirstFourDays) If ISOWeeknum 52 Then If DatePart("ww", dt + 7, vbMonday, vbFirstFourDays) = 2 Then ISOWeeknum = 1 End If End If End Function ==================================== --ron Hi Ron! Your ISOWeeknum(dt As Date) has the advantage of sidestepping the bug in Datepart in a clear way. But some of the errors remain. Sun 2 jan 2101 still gives week 53, which should be 52 like Sat 1 jan 2101. A bit into the future, I'll admit. Staying with Datepart there is this: Function ISOWknum(d As Date) '25/12/1899-26/12/9999 ISOWknum = DatePart("ww", ((d + 5) \ 7) * 7, 2, 2) End Function It looks at the Saturday of the same week. Otherwise I'm quite proud of this Function WkIso(d) '..1/1/100-31/12/9999.. WkIso = ((((d + 692501) \ 7 Mod 20871) * 28 + 4383) Mod 146096 Mod 1461) \ 28 + 1 End Function It is very fast, doesn't need datefunctions. Hans. Very good Hans! I was not aware of that BUG in DatePart. (And, apparently, neither was Microsoft, as the only BUG they mention is the one for Mondays). I like both of your workarounds. The one I came up with is not as fast as yours, but it does clarify the bugs, I think. I haven't tested it yet, though: ======================================== Function ISOWeeknum(dt As Date) As Integer ISOWeeknum = DatePart("ww", dt + (Weekday(dt, vbMonday) < 1), _ vbMonday, vbFirstFourDays) If DatePart("ww", dt + 7, vbMonday, vbFirstFourDays) = 2 _ Then ISOWeeknum = 1 End Function =========================================== --ron Ron, if you will accept my testresult, your function is true for all (VB) dates 4 jan 100 - 24 dec 9999! And still clear (enough) :-) You go back 1 day except on mondays. I also looked for a simple modification, but could not come up with anything better. OK and thanks, Hans. |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Week Number using VBA in Excel
On Sun, 5 Aug 2007 16:55:56 +0200, "Hans Terkelsen" <dk wrote:
"Ron Rosenfeld" wrote in message ... On Sun, 5 Aug 2007 13:32:59 +0200, "Hans Terkelsen" <dk wrote: "Ron Rosenfeld" wrote in message ... On Sat, 4 Aug 2007 08:00:01 -0700, Kam wrote: Hi Can you please help to get this script run in excel. as I am finding difficulties in running. Public Function ISOWeekNum(AnyDate As Date, _ Optional WhichFormat As Variant) As Integer ' ' WhichFormat: missing or < 2 then returns week number, ' = 2 then YYWW ' Dim ThisYear As Integer Dim PreviousYearStart As Date Dim ThisYearStart As Date Dim NextYearStart As Date Dim YearNum As Integer ThisYear = Year(AnyDate) ThisYearStart = YearStart(ThisYear) PreviousYearStart = YearStart(ThisYear - 1) NextYearStart = YearStart(ThisYear + 1) Select Case AnyDate Case Is = NextYearStart ISOWeekNum = (AnyDate - NextYearStart) \ 7 + 1 YearNum = Year(AnyDate) + 1 Case Is < ThisYearStart ISOWeekNum = (AnyDate - PreviousYearStart) \ 7 + 1 YearNum = Year(AnyDate) - 1 Case Else ISOWeekNum = (AnyDate - ThisYearStart) \ 7 + 1 YearNum = Year(AnyDate) End Select If IsMissing(WhichFormat) Then Exit Function End If If WhichFormat = 2 Then ISOWeekNum = CInt(Format(Right(YearNum, 2), "00") & _ Format(ISOWeekNum, "00")) End If End Function What problems are you having? There are simpler algorithms. For example, to just get the ISOWeeknumber: ================================== Function ISOWeeknum(dt As Date) As Integer ISOWeeknum = DatePart("ww", dt, vbMonday, vbFirstFourDays) If ISOWeeknum 52 Then If DatePart("ww", dt + 7, vbMonday, vbFirstFourDays) = 2 Then ISOWeeknum = 1 End If End If End Function ==================================== --ron Hi Ron! Your ISOWeeknum(dt As Date) has the advantage of sidestepping the bug in Datepart in a clear way. But some of the errors remain. Sun 2 jan 2101 still gives week 53, which should be 52 like Sat 1 jan 2101. A bit into the future, I'll admit. Staying with Datepart there is this: Function ISOWknum(d As Date) '25/12/1899-26/12/9999 ISOWknum = DatePart("ww", ((d + 5) \ 7) * 7, 2, 2) End Function It looks at the Saturday of the same week. Otherwise I'm quite proud of this Function WkIso(d) '..1/1/100-31/12/9999.. WkIso = ((((d + 692501) \ 7 Mod 20871) * 28 + 4383) Mod 146096 Mod 1461) \ 28 + 1 End Function It is very fast, doesn't need datefunctions. Hans. Very good Hans! I was not aware of that BUG in DatePart. (And, apparently, neither was Microsoft, as the only BUG they mention is the one for Mondays). I like both of your workarounds. The one I came up with is not as fast as yours, but it does clarify the bugs, I think. I haven't tested it yet, though: ======================================== Function ISOWeeknum(dt As Date) As Integer ISOWeeknum = DatePart("ww", dt + (Weekday(dt, vbMonday) < 1), _ vbMonday, vbFirstFourDays) If DatePart("ww", dt + 7, vbMonday, vbFirstFourDays) = 2 _ Then ISOWeeknum = 1 End Function =========================================== --ron Ron, if you will accept my testresult, your function is true for all (VB) dates 4 jan 100 - 24 dec 9999! And still clear (enough) :-) You go back 1 day except on mondays. I also looked for a simple modification, but could not come up with anything better. OK and thanks, Hans. Thank you, Hans. I wonder if MS will ever correct these bugs. They've been around for a long time. --ron |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Week Number using VBA in Excel
In , Ron Rosenfeld
spake thusly: I wonder if MS will ever correct these bugs. They've been around for a long time. Somehow I doubt it. By way of example, In another Office app that I use, Outlook 2002 (and I have been using Outlook since 1996), there is still a bug where no test for race condition is done before the message that is currently in focus (highlighted) is acted upon. So, for example, if I have a message highlighted and am about to delete it and am pressing the Delete key, or am about to move it to some folder and am dragging it, and at that moment another message comes in, the other message gets deleted or moved instead! I find this uncorrected longstanding bug astonishingly, incorrigibly bad. I wouldn't mind knowing if it's still in the current version. Dallman |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Week Number using VBA in Excel
Hi Hans
I not test your function but do you get the same results as the VBA function and the formulas on my page http://www.rondebruin.nl/weeknumber.htm#information See the Calendar workbook -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Hans Terkelsen" <dk wrote in message ... "Ron Rosenfeld" wrote in message ... On Sat, 4 Aug 2007 08:00:01 -0700, Kam wrote: Hi Can you please help to get this script run in excel. as I am finding difficulties in running. Public Function ISOWeekNum(AnyDate As Date, _ Optional WhichFormat As Variant) As Integer ' ' WhichFormat: missing or < 2 then returns week number, ' = 2 then YYWW ' Dim ThisYear As Integer Dim PreviousYearStart As Date Dim ThisYearStart As Date Dim NextYearStart As Date Dim YearNum As Integer ThisYear = Year(AnyDate) ThisYearStart = YearStart(ThisYear) PreviousYearStart = YearStart(ThisYear - 1) NextYearStart = YearStart(ThisYear + 1) Select Case AnyDate Case Is = NextYearStart ISOWeekNum = (AnyDate - NextYearStart) \ 7 + 1 YearNum = Year(AnyDate) + 1 Case Is < ThisYearStart ISOWeekNum = (AnyDate - PreviousYearStart) \ 7 + 1 YearNum = Year(AnyDate) - 1 Case Else ISOWeekNum = (AnyDate - ThisYearStart) \ 7 + 1 YearNum = Year(AnyDate) End Select If IsMissing(WhichFormat) Then Exit Function End If If WhichFormat = 2 Then ISOWeekNum = CInt(Format(Right(YearNum, 2), "00") & _ Format(ISOWeekNum, "00")) End If End Function What problems are you having? There are simpler algorithms. For example, to just get the ISOWeeknumber: ================================== Function ISOWeeknum(dt As Date) As Integer ISOWeeknum = DatePart("ww", dt, vbMonday, vbFirstFourDays) If ISOWeeknum 52 Then If DatePart("ww", dt + 7, vbMonday, vbFirstFourDays) = 2 Then ISOWeeknum = 1 End If End If End Function ==================================== --ron Hi Ron! Your ISOWeeknum(dt As Date) has the advantage of sidestepping the bug in Datepart in a clear way. But some of the errors remain. Sun 2 jan 2101 still gives week 53, which should be 52 like Sat 1 jan 2101. A bit into the future, I'll admit. Staying with Datepart there is this: Function ISOWknum(d As Date) '25/12/1899-26/12/9999 ISOWknum = DatePart("ww", ((d + 5) \ 7) * 7, 2, 2) End Function It looks at the Saturday of the same week. Otherwise I'm quite proud of this Function WkIso(d) '..1/1/100-31/12/9999.. WkIso = ((((d + 692501) \ 7 Mod 20871) * 28 + 4383) Mod 146096 Mod 1461) \ 28 + 1 End Function It is very fast, doesn't need datefunctions. Hans. |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
Week Number using VBA in Excel
On Sun, 5 Aug 2007 22:00:51 +0200, "Ron de Bruin"
wrote: Hi Hans I not test your function but do you get the same results as the VBA function and the formulas on my page http://www.rondebruin.nl/weeknumber.htm#information See the Calendar workbook I checked Daniel's VBA algorithm and it seems to give all the same (correct) results. Of course, he is a true expert with date formulas! --ron |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
Week Number using VBA in Excel
"Ron de Bruin" wrote in message ... Hi Hans I not test your function but do you get the same results as the VBA function and the formulas on my page http://www.rondebruin.nl/weeknumber.htm#information See the Calendar workbook Hi Ron d B! I have been slow to answer, sorry. Yes, I get the same results as your function and formula gives. The only difference is a few days at the start of the dates. We can consider Daniel Mahers function as a reference. My checker runs through all the dates, and compares the putative(?) ISO weeknumber function to a running week counter that follows the appropriate rules. Takes only a few minutes in VB. DM's function is true 4/1/100-31/12/9999 according to that checker. The first 3 days it errors, if anybody should care. Ptolemy maybe:-) Your worksheet function likewise, 2/1/1900-31/12/9999, agrees with the others. Did you want a second opinion on the Calendar workbook? Useful and correct :-) A reverse weeknumber function, from ISOweekyear and weeknumber to a date, agrees with your date for first monday 1901 to 9999, but not for 1900 as you know. Yours is the correct date. Another way to sidestep the 29/2/1900 issue, is to add 400 years to the year in formulas. All dates, weekdays, weeknumbers repeat after 400 years, and 29/2/2300 does not exist in Excel. Then the calendar could go back to 1500! I think I'll keep it as it is, Thankyou Ron, Greetings Hans. |
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
Week Number using VBA in Excel
Thank for your reply Hans
I an updating all my webpages on the moment. When I do the Week number and ISO page I will check all the stuff on it and maybe do a small update Have a nice day -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Hans Terkelsen" <dk wrote in message ... "Ron de Bruin" wrote in message ... Hi Hans I not test your function but do you get the same results as the VBA function and the formulas on my page http://www.rondebruin.nl/weeknumber.htm#information See the Calendar workbook Hi Ron d B! I have been slow to answer, sorry. Yes, I get the same results as your function and formula gives. The only difference is a few days at the start of the dates. We can consider Daniel Mahers function as a reference. My checker runs through all the dates, and compares the putative(?) ISO weeknumber function to a running week counter that follows the appropriate rules. Takes only a few minutes in VB. DM's function is true 4/1/100-31/12/9999 according to that checker. The first 3 days it errors, if anybody should care. Ptolemy maybe:-) Your worksheet function likewise, 2/1/1900-31/12/9999, agrees with the others. Did you want a second opinion on the Calendar workbook? Useful and correct :-) A reverse weeknumber function, from ISOweekyear and weeknumber to a date, agrees with your date for first monday 1901 to 9999, but not for 1900 as you know. Yours is the correct date. Another way to sidestep the 29/2/1900 issue, is to add 400 years to the year in formulas. All dates, weekdays, weeknumbers repeat after 400 years, and 29/2/2300 does not exist in Excel. Then the calendar could go back to 1500! I think I'll keep it as it is, Thankyou Ron, Greetings Hans. |
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
Week Number using VBA in Excel
Hi Ron
Of course, he is a true expert with date formulas! Indeed, a superstar -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Ron Rosenfeld" wrote in message ... On Sun, 5 Aug 2007 22:00:51 +0200, "Ron de Bruin" wrote: Hi Hans I not test your function but do you get the same results as the VBA function and the formulas on my page http://www.rondebruin.nl/weeknumber.htm#information See the Calendar workbook I checked Daniel's VBA algorithm and it seems to give all the same (correct) results. Of course, he is a true expert with date formulas! --ron |
#15
Posted to microsoft.public.excel.misc
|
|||
|
|||
Week Number using VBA in Excel
What I have done I pasted ISO week & ISO year start script into modules &
after that I saved that file. After opening the same file have entered some dates into excel but it is not giving me week number. Can you please guide me how to do it as I am not the VB script expert. Can you also let me know is that possible that I can get week number in desired column number. Appreciate your help. Kam. "Rick Rothstein (MVP - VB)" wrote: And those difficulties you are having are what? The more details you give us, the better able we are to figure out how to help you. It looks like you copied John Green's ISOWeekNum function correctly... did you also copy the YearStart function which the ISOWeekNum function depends on? Rick "Kam" wrote in message ... Hi Can you please help to get this script run in excel. as I am finding difficulties in running. Public Function ISOWeekNum(AnyDate As Date, _ Optional WhichFormat As Variant) As Integer ' ' WhichFormat: missing or < 2 then returns week number, ' = 2 then YYWW ' Dim ThisYear As Integer Dim PreviousYearStart As Date Dim ThisYearStart As Date Dim NextYearStart As Date Dim YearNum As Integer ThisYear = Year(AnyDate) ThisYearStart = YearStart(ThisYear) PreviousYearStart = YearStart(ThisYear - 1) NextYearStart = YearStart(ThisYear + 1) Select Case AnyDate Case Is = NextYearStart ISOWeekNum = (AnyDate - NextYearStart) \ 7 + 1 YearNum = Year(AnyDate) + 1 Case Is < ThisYearStart ISOWeekNum = (AnyDate - PreviousYearStart) \ 7 + 1 YearNum = Year(AnyDate) - 1 Case Else ISOWeekNum = (AnyDate - ThisYearStart) \ 7 + 1 YearNum = Year(AnyDate) End Select If IsMissing(WhichFormat) Then Exit Function End If If WhichFormat = 2 Then ISOWeekNum = CInt(Format(Right(YearNum, 2), "00") & _ Format(ISOWeekNum, "00")) End If End Function |
#16
Posted to microsoft.public.excel.misc
|
|||
|
|||
Week Number using VBA in Excel
"Ron de Bruin" wrote in message ... Thank for your reply Hans I an updating all my webpages on the moment. When I do the Week number and ISO page I will check all the stuff on it and maybe do a small update Have a nice day Regards Ron de Bruin http://www.rondebruin.nl/tips.htm You too, Ron. I would be pleased if you included my WkIso() function, but you might decide that it looks too cryptic. It had to work with integers, to avoid small binary inaccuracies like 1.2-1.1-0.1 < 0, and that made for big unrecognizeable numbers. I suspect that the Datepart bug comes from a related problem. Greetings, Hans. "Hans Terkelsen" <dk wrote in message ... "Ron de Bruin" wrote in message ... Hi Hans I not test your function but do you get the same results as the VBA function and the formulas on my page http://www.rondebruin.nl/weeknumber.htm#information See the Calendar workbook Hi Ron d B! I have been slow to answer, sorry. Yes, I get the same results as your function and formula gives. The only difference is a few days at the start of the dates. We can consider Daniel Mahers function as a reference. My checker runs through all the dates, and compares the putative(?) ISO weeknumber function to a running week counter that follows the appropriate rules. Takes only a few minutes in VB. DM's function is true 4/1/100-31/12/9999 according to that checker. The first 3 days it errors, if anybody should care. Ptolemy maybe:-) Your worksheet function likewise, 2/1/1900-31/12/9999, agrees with the others. Did you want a second opinion on the Calendar workbook? Useful and correct :-) A reverse weeknumber function, from ISOweekyear and weeknumber to a date, agrees with your date for first monday 1901 to 9999, but not for 1900 as you know. Yours is the correct date. Another way to sidestep the 29/2/1900 issue, is to add 400 years to the year in formulas. All dates, weekdays, weeknumbers repeat after 400 years, and 29/2/2300 does not exist in Excel. Then the calendar could go back to 1500! I think I'll keep it as it is, Thankyou Ron, Greetings Hans. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
group sales by week and week number | Excel Discussion (Misc queries) | |||
Can Excel turn % hrs worked/week put in a cell into a real number | Excel Worksheet Functions | |||
How do I set up a week by week skill training schedule in excel? | Excel Discussion (Misc queries) | |||
how to get week number in month in excel ? | Charts and Charting in Excel | |||
I need week number in excell from a date, first week must be mini. | Excel Discussion (Misc queries) |