It’s always a little embarrassing when you roll your own Function to achieve some functionality that already exists in Excel or Access.

Recently, I stumbled across a user-defined function designed to find a week number in Access. I asked the owner of this code why he wrote a function to do this when Access has one. He gave me the ‘thousand-yard stare’, then he went on to explain that he couldn’t find the WEEKNUM function in Access.

In Excel, the WEEKNUM function converts a given date into a week number. For example, this would give you the week number in which the current date falls.

**=WEEKNUM(TODAY())**

There is no such function in Access. You won’t find WEEKNUM in the list of available Access Functions. There’s is WEEKDAY and WEEKDAYNAME, but no WEEKNUM.

So he naturally started to write his own function.

Well, it turns out you can get to a week number in Access by using the **DATEPART** function. Like so:

**DatePart(“ww”,Date())**

You can also use this in Excel VBA to get around calling Application.WorksheetFunction.WeekNum

Today, my friend still uses his own Week Number user-defined function – mainly out of spite. I suggested that he create a function that returns the average of range of numbers. He told me to shut up.

Have you ever rolled your own user-defined function only to find out that the functionality already existed?

I’m waiting for someone to point out the difference between US and European definitions for Week Numbers.

Hi Jon

Why not

See

http://msdn.microsoft.com/en-us/library/bb277364.aspx

Ron: Interesting. I did not know there was an international impact using WEEKNUM. Good link.

Ron: Wouldn’t this give you an ISO week number?

DatePart(“ww”, Date, vbMonday, vbFirstFullWeek)

Hi Mike

There are a few bugs when you use this so this is not a option

See

http://support.microsoft.com/?kbid=200299

Submiting this test procedure to prove out the bug:

[VB]

Sub Test2()

Dim GL_Dt As Date

Dim I As Long

For I = 39814 To 100000 ‘ test for more then 27 years

GL_Dt = I

If Int(([GL_Dt] – DateSerial(Year([GL_Dt] – Weekday([GL_Dt] – 1) + 4), 1, 3) + Weekday(DateSerial(Year([GL_Dt] – Weekday([GL_Dt] – 1) + 4), 1, 3)) + 5) / 7) _

<> DatePart(“ww”, [GL_Dt], vbMonday, vbFirstFourDays) Then MsgBox “Error in date ” & I

Next I

End Sub

Public Function IsoWeekNumber(d1 As Date) As Integer

‘ Attributed to Daniel Maher

Dim d2 As Long

d2 = DateSerial(Year(d1 – Weekday(d1 – 1) + 4), 1, 3)

IsoWeekNumber = Int((d1 – d2 + Weekday(d2) + 5) / 7)

End Function

[/VB]

In Access:

Format(Date(),”ww”)

Format ([YourField],”ww”)

Clng(Format(Date(),”ww”))

This will not return the correct weeknumber for all days. Check out this KB article and you will understand why he told….(DATAPIG SAYS PLAY NICE HENRY).

http://support.microsoft.com/kb/200299

I can hardly call this a PowerTip. A good function in Access for ISO (european) weeknumbers and the year that goes with it, that would be a PowerTip.

Wow, many rather logical tips! I appreciate you crafting this posting and the remainder of your internet site is outstanding!

hi,

sorry for the novice question, but to which area is the format applied?

In Access:

Format(Date(),”ww”)

Format ([YourField],”ww”)

Clng(Format(Date(),”ww”))

thank you

Thakyou very much! Good hint.