Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Week number in vba
I need to find a way for vba to determine the week number of a given date in
a year. I do not want to depend on Excel's Analysis TookPak. Can anyone help? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Week number in vba
See
http://www.rondebruin.nl/weeknumber.htm http://www.rondebruin.nl/weeknumber.htm#information -- Gary "Sam" wrote in message ... I need to find a way for vba to determine the week number of a given date in a year. I do not want to depend on Excel's Analysis TookPak. Can anyone help? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Week number in vba
Sam,
The hard part is determining the reference date (when does week 1 begin for the year in question?). Below is an example I helped someone with where his industry considered week 1 to be the first full week in July with weeks beginning on Sunday. This probably isn't the same as your requirement but it might give a few hints. __________________________________ Function WkCount(Optional dtmDate As Date) ' Test that an argument was actually received If dtmDate = 0 Then WkCount = "" Exit Function End If Dim dtmRefJulOne As Date Dim dtmTemp As Date Dim intElapsedDays As Integer ' Determine most recent July 1st If Month(dtmDate) < 7 Then dtmRefJulOne = DateSerial(Year(dtmDate) - 1, 7, 1) Else dtmRefJulOne = DateSerial(Year(dtmDate), 7, 1) End If ' Determine first Sunday in most recent July dtmTemp = dtmRefJulOne Do While Weekday(dtmTemp) < vbSunday dtmTemp = dtmTemp + 1 Loop ' Handle case for a date in July prior to the first Sunday in July If Month(dtmDate) = 7 And dtmDate < dtmTemp Then dtmRefJulOne = DateSerial(Year(dtmDate) - 1, 7, 1) dtmTemp = dtmRefJulOne Do While Weekday(dtmTemp) < vbSunday dtmTemp = dtmTemp + 1 Loop End If ' Determine days elapsed since reference Sunday in July ' Divide by 7 and round up for a week count, format as integer intElapsedDays = dtmDate - dtmTemp WkCount = FormatNumber(CInt(Int((intElapsedDays / 7) + 1)), 0) End Function __________________________________ Steve Yandl "Sam" wrote in message ... I need to find a way for vba to determine the week number of a given date in a year. I do not want to depend on Excel's Analysis TookPak. Can anyone help? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Week number in vba
I have also info on MSDN now Gary
http://msdn2.microsoft.com/en-us/library/bb277364.aspx -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Gary Keramidas" <GKeramidasATmsn.com wrote in message ... See http://www.rondebruin.nl/weeknumber.htm http://www.rondebruin.nl/weeknumber.htm#information -- Gary "Sam" wrote in message ... I need to find a way for vba to determine the week number of a given date in a year. I do not want to depend on Excel's Analysis TookPak. Can anyone help? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Week number in vba
ok, thanks for the link
-- Gary "Ron de Bruin" wrote in message ... I have also info on MSDN now Gary http://msdn2.microsoft.com/en-us/library/bb277364.aspx -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Gary Keramidas" <GKeramidasATmsn.com wrote in message ... See http://www.rondebruin.nl/weeknumber.htm http://www.rondebruin.nl/weeknumber.htm#information -- Gary "Sam" wrote in message ... I need to find a way for vba to determine the week number of a given date in a year. I do not want to depend on Excel's Analysis TookPak. Can anyone help? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Week number in vba
Thanks Steve.
With the same idea but using date math instead of loops : Function WeekCnt(Optional dtmDate As Date) As Variant ' Test that an argument was actually received If dtmDate = 0 Then WeekCnt = "" Exit Function End If Dim July08 As Date, FirstSunJuly As Date July08 = DateSerial(Year(dtmDate), 7, 8) FirstSunJuly = July08 - Weekday(July08 - 1) ' 1st Sunday If dtmDate < FirstSunJuly Then July08 = DateSerial(Year(dtmDate) - 1, 7, 8) ' Correct Year FirstSunJuly = July08 - Weekday(July08 - 1) ' 1st Sunday End If ' Determine days elapsed since reference Sunday in July ' Divide by 7 and round up for a week count, format as integer WeekCnt = Int(((dtmDate - FirstSunJuly) / 7) + 1) End Function Regards, Daniel M. "Steve Yandl" wrote in message ... Sam, The hard part is determining the reference date (when does week 1 begin for the year in question?). Below is an example I helped someone with where his industry considered week 1 to be the first full week in July with weeks beginning on Sunday. This probably isn't the same as your requirement but it might give a few hints. __________________________________ Function WkCount(Optional dtmDate As Date) ' Test that an argument was actually received If dtmDate = 0 Then WkCount = "" Exit Function End If Dim dtmRefJulOne As Date Dim dtmTemp As Date Dim intElapsedDays As Integer ' Determine most recent July 1st If Month(dtmDate) < 7 Then dtmRefJulOne = DateSerial(Year(dtmDate) - 1, 7, 1) Else dtmRefJulOne = DateSerial(Year(dtmDate), 7, 1) End If ' Determine first Sunday in most recent July dtmTemp = dtmRefJulOne Do While Weekday(dtmTemp) < vbSunday dtmTemp = dtmTemp + 1 Loop ' Handle case for a date in July prior to the first Sunday in July If Month(dtmDate) = 7 And dtmDate < dtmTemp Then dtmRefJulOne = DateSerial(Year(dtmDate) - 1, 7, 1) dtmTemp = dtmRefJulOne Do While Weekday(dtmTemp) < vbSunday dtmTemp = dtmTemp + 1 Loop End If ' Determine days elapsed since reference Sunday in July ' Divide by 7 and round up for a week count, format as integer intElapsedDays = dtmDate - dtmTemp WkCount = FormatNumber(CInt(Int((intElapsedDays / 7) + 1)), 0) End Function __________________________________ Steve Yandl "Sam" wrote in message ... I need to find a way for vba to determine the week number of a given date in a year. I do not want to depend on Excel's Analysis TookPak. Can anyone help? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
week number | Excel Worksheet Functions | |||
Week Number. | Excel Worksheet Functions | |||
Week Number | Excel Discussion (Misc queries) | |||
group sales by week and week number | Excel Discussion (Misc queries) | |||
I need week number in excell from a date, first week must be mini. | Excel Discussion (Misc queries) |