Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel dates seem to confuse me ...
OK, here goes,
I have to create a function in Excel that shows a date string of the format "TLyyyymmww" based on the following parameters : a) "ww" is the week number of the week of that date, all weeks begin on a Sunday b) "mm" is the month number of the week described in a) c) "yyy" is the year number of the week described in a) What I do not understand is why must I trap the occurance of "30-Dec-2007". If I do not then then answer will be "TL2007.12.53" instead of "TL2008.01.01". I know this date does not work correctly but wondered if anyone knew WHY it must be trapped. and if there anr any other possible dated that will yield the same error '''''''''''''''''''''''''''''''''''''''''''''''''' '''''''' '''''''''''''''''''''''''''''''''''''''''''''''''' '''''''' Function TILPeriod(xDate As Date) As String Dim d As Date, w As Integer, y As Integer, m As Integer d = Int((xDate - vbSunday) / 7) * 7 + vbSunday w = Format(xDate, "ww", vbSunday, vbFirstFourDays) y = Year(d) m = Month(d) ' in case this December week should be 'moved to January next year If (w = 1) And (m = 12) Then m = 1 y = y + 1 'WHY !!!!!!!!!!!!!!!!! ElseIf (d = #12/30/2007#) Then w = 1 m = 1 y = y + 1 End If TILPeriod = "TL" & y & "." & Format(m, "00") & "." & Format(w, "00") End Function '''''''''''''''''''''''''''''''''''''''''''''''''' '''''''' '''''''''''''''''''''''''''''''''''''''''''''''''' '''''''' Clear as mud I suppose, but I do not know if this is a coding or mathematical problem, and any help yould be great Sean "Just press the off switch, and go to sleep!" *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel dates seem to confuse me ...
This looks like a bug to me. I wrote the following little function, then put a
date series starting with 12/23/2007 in A1:A16. In B1, I put the formula =xWeekNum(A1) and copied it down. It gave me 53 for Sunday, 12/30/2007; this 53rd week of 2007 has only 1 day, and the 1st week of 2008 has only 6 days! Function xWeekNum(aDate As Date) xWeekNum = DatePart("ww", aDate, vbSunday, vbFirstFourDays) End Function I wrote the following routine to list all weeks from the end of 1900 through the beginning of 2201 that don't have 7 days. There were 68 weeks that had only 1 or 6 days. I have a VBA routine that calculates ISO week numbers. I will try it an see if it has the same problem. I will post back with the results. If it's OK, I'll give you that routine. Option Explicit Sub FindProblemDates() Dim d As Double Dim ErrCount As Long Dim ErrList() As Double Dim n As Long Dim w As Long Dim PrevWeek As Long Const Year1 As Long = 1900 Const YearN As Long = 2200 'maximum of 2 errors per year ReDim ErrList(1 To (YearN - Year1 + 2) * 2, 1 To 3) 'loop until we get to the first week of 2000 d = DateSerial(Year1, 12, 29) Do Until DatePart("ww", d, vbSunday, vbFirstFourDays) = 1 d = d + 1 Loop n = 1 PrevWeek = 1 ErrCount = 0 For d = d + 1 To DateSerial(YearN + 1, 1, 10) w = DatePart("ww", d, vbSunday, vbFirstFourDays) If w < PrevWeek Then 'all weeks should have 7 days If n < 7 Then ErrCount = ErrCount + 1 ErrList(ErrCount, 1) = d ErrList(ErrCount, 2) = PrevWeek ErrList(ErrCount, 3) = n End If n = 1 PrevWeek = w Else n = n + 1 End If Next d ActiveSheet.Columns(1).Clear With ActiveSheet.Cells(1, 1).Resize(ErrCount, 3) .Value2 = ErrList() .NumberFormat = "0" .Columns(1).NumberFormat = "mm/dd/yyyy" End With End Sub On Thu, 12 Aug 2004 08:36:28 -0700, Sean Howard wrote: OK, here goes, I have to create a function in Excel that shows a date string of the format "TLyyyymmww" based on the following parameters : a) "ww" is the week number of the week of that date, all weeks begin on a Sunday b) "mm" is the month number of the week described in a) c) "yyy" is the year number of the week described in a) What I do not understand is why must I trap the occurance of "30-Dec-2007". If I do not then then answer will be "TL2007.12.53" instead of "TL2008.01.01". I know this date does not work correctly but wondered if anyone knew WHY it must be trapped. and if there anr any other possible dated that will yield the same error ''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''' ''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''' Function TILPeriod(xDate As Date) As String Dim d As Date, w As Integer, y As Integer, m As Integer d = Int((xDate - vbSunday) / 7) * 7 + vbSunday w = Format(xDate, "ww", vbSunday, vbFirstFourDays) y = Year(d) m = Month(d) ' in case this December week should be 'moved to January next year If (w = 1) And (m = 12) Then m = 1 y = y + 1 'WHY !!!!!!!!!!!!!!!!! ElseIf (d = #12/30/2007#) Then w = 1 m = 1 y = y + 1 End If TILPeriod = "TL" & y & "." & Format(m, "00") & "." & Format(w, "00") End Function ''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''' ''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''' Clear as mud I suppose, but I do not know if this is a coding or mathematical problem, and any help yould be great Sean "Just press the off switch, and go to sleep!" *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel dates seem to confuse me ...
I've attached code for the ISO weeknumber below. For the date range
end-of-1900 through 1st week in 2201, all weeks have 7 days. But there's a hitch.... the ISO standard says that weeks begin on *Monday*, not Sunday. Here's an excerpt from the ISO definition: "Week 01 of a year is per definition the first week that has the Thursday in this year, which is equivalent to the week that contains the fourth day of January. In other words, the first week of a new year is the week that has the majority of its days in the new year. Week 01 might also contain days from the previous year and the week before week 01 of a year is the last week (52 or 53) of the previous year even if it contains days from the new year. A week starts with Monday (day 1) and ends with Sunday (day 7)." Here's my code for the ISO week number. As far as starting the week on Sunday, I *think* it requires only that you change vbMonday to vbSunday in the ISOYearStart routine, but I haven't tested this. As you can see from this code, the problem resolves to determining the day on which Week1 begins. Once you have that, you get the week number by N = (TheDate - YearStart) \ 7 + 1 Function ISOWeekNum(TheDate As Date, Optional NumFormat As Long = 1) As Long Dim y As Long Dim YearStart As Date Dim NextYearStart As Date Dim N As Long y = Year(TheDate) YearStart = ISOYearStart(y) NextYearStart = ISOYearStart(y + 1) If TheDate = NextYearStart Then y = y + 1 YearStart = NextYearStart ElseIf TheDate < YearStart Then y = y - 1 YearStart = ISOYearStart(y) End If N = (TheDate - YearStart) \ 7 + 1 Select Case NumFormat Case 1: ISOWeekNum = N Case 2: ISOWeekNum = (y Mod 100) * 100 + N Case 3: ISOWeekNum = y * 100 + N End Select End Function Function ISOYearStart(TheYear As Long) As Date Dim Jan4 As Date Jan4 = DateSerial(TheYear, 1, 4) ISOYearStart = Jan4 - Weekday(Jan4, vbMonday) + 1 End Function |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel dates seem to confuse me ...
Here's another test routine that compares the week number returned by VBA's
DatePart with my ISO number routine. I adjusted the latter to start the week on Sunday rather than Monday. I found 34 discrepancies in the period from Jan 4, 1900 through Jan 11, 2201. For Sat Jan 1, 2101, VBA gave 53 and ISO 52; for Sat Jan 3, 2201, results were 54 [sic!] and 53. The other 32 instances all involved the last Sunday of the calendar year; VBA's result was 53 and the ISO routine gave 1. One of them was the Dec 30, 2007 date that you've already discovered. Here's my lastest ISO routine and test code: Option Explicit Sub WEEKNOProblems() Dim d As Date Dim ErrCount As Long Dim ErrList() As Double Dim w1 As Long Dim w2 As Long Const Year1 As Long = 1900 Const YearN As Long = 2200 'maximum of 2 errors per year ReDim ErrList(1 To (YearN - Year1 + 1) * 2 + 2, 1 To 3) ErrCount = 0 For d = DateSerial(Year1, 1, 4) To DateSerial(YearN + 1, 1, 11) w1 = DatePart("ww", d, vbSunday, vbFirstFourDays) w2 = ISOWeekNum(d, vbSunday) If w1 < w2 Then ErrCount = ErrCount + 1 ErrList(ErrCount, 1) = CDbl(d) ErrList(ErrCount, 2) = w1 ErrList(ErrCount, 3) = w2 End If Next d ActiveSheet.Columns(1).Resize(, 3).Clear If ErrCount = 0 Then MsgBox "No errors!", vbOKOnly Else With ActiveSheet .Cells(1, 1).Resize(1, 3).Value = Array("Date", "VBA", "ISO") With .Cells(2, 1).Resize(ErrCount, 3) .Value2 = ErrList() .NumberFormat = "0" .Columns(1).NumberFormat = "mm/dd/yyyy" End With End With End If End Sub Function ISOWeekNum(TheDate As Date, _ Optional StartDay As Long = vbMonday, _ Optional NumFormat As Long = 1) As Long Dim y As Long Dim YearStart As Date Dim NextYearStart As Date Dim n As Long y = Year(TheDate) YearStart = ISOYearStart(y, StartDay) NextYearStart = ISOYearStart(y + 1, StartDay) If TheDate = NextYearStart Then 'end of December y = y + 1 YearStart = NextYearStart ElseIf TheDate < YearStart Then 'early January y = y - 1 YearStart = ISOYearStart(y, StartDay) End If n = (TheDate - YearStart) \ 7 + 1 Select Case NumFormat Case 1: ISOWeekNum = n Case 2: ISOWeekNum = (y Mod 100) * 100 + n Case 3: ISOWeekNum = y * 100 + n End Select End Function Private Function ISOYearStart(TheYear As Long, _ Optional StartDay As Long = vbMonday) As Date Dim Jan4 As Date Jan4 = DateSerial(TheYear, 1, 4) ISOYearStart = Jan4 - Weekday(Jan4, StartDay) + 1 End Function |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel dates seem to confuse me ...
Hello Myrna
Thanks for the code, I will go through it thoroughly soon. However I still do not understand why such extensive code, is needed to yield such a simple result. Is this a bug(!) in Excel, or should all calendars be changed to "the weeks according to Uncle Bill". Sean "Just press the off switch, and go to sleep!" *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Why does this confuse me?? -- ? on Offset function | Excel Worksheet Functions | |||
confuse on vlookup, index match | Excel Discussion (Misc queries) | |||
confuse on vlookup, index match | Excel Discussion (Misc queries) | |||
confuse on vlookup, index match | Excel Discussion (Misc queries) | |||
confuse on vlookup, index match | Excel Discussion (Misc queries) |