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

Assume date is in A1:

in B1:

=isoweeknum(a1)

B1 will contain the week number for date in A1

HTH

"Kam" wrote:

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

An easyway is to just add your week number to the system week number (or
subtract) with a function like =WEEKNUM(A1,1)
--
-John
Please rate when your question is answered to help us and others know what
is helpful.


"Kam" wrote:

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