Week Number (1 Viewer)

[BenSteckler]

Is there any visual Basic comand that gives you the current week number from a given date. Like the Month([Date]) function gives 10 for Oct 31, 2001 (or whatever date supplied.)

 

[ntp]

Try this function

Private Function WeekOfMonth(dte as Date) as Integer
WeekOfMonth = DateDiff("ww", DateSerial(Year(dte), Month(dte), "01"), dte, vbMonday, vbFirstFourDays) + 1
End Sub

You'll get the week.

ntp

Additional Point:
My solution gives you the week in the current month, Rich's solution is the week from the beginning of the year.

ntp

[This message has been edited by ntp (edited 02-22-2001).]

 

[Rich]

DatePart("ww",[YourDateField])
type in ww in help index for various start day week options.

 

[rcollman]

Ben, ntp and Rich,
Thanks. I was trying to create a text field with YYWW in Access. I'm not a VB person and figured out how to do it with many functions in Excel. I had to get to the custom format "00" to get 1/1/01 to come out 0101.

"WW" is sweet. I used build and did something like =format (1/1/01,"YY")&format (1/1/01, "WW") in an update query. Worked first time !! Many thanks.