WEEKNUM calculations for week stating with Tuesday

[P C Verma]

While calculating WEEKNUM stating from Tuesday to Friday i am getting error
massage #NUM# but for week staring from Sunday and Monday it calculating
correctly. What could be the reason and how to work around it correctly.

 

[Mike H]

Maybe

=IF(WEEKDAY(A1,2)>1,WEEKNUM(A1,2)+1,WEEKNUM(A1,2))

Mike

 

[Stefi]

There is no known place in the world where week starts with Tuesday-Friday,
therefore Excel doesn't have this feature. What do you want to do?

Regards,
Stefi

„P C Verma†ezt írta:

 

[David Biddulph]

What is your formula? What values are in the cells leading into the
formula? What value did you get as a result? What value did you expect?
What formula is working correctly for what data?

 

[Ron Rosenfeld]

On Wed, 11 Jun 2008 02:14:00 -0700, P C Verma <P C

While calculating WEEKNUM stating from Tuesday to Friday i am getting error
massage #NUM# but for week staring from Sunday and Monday it calculating
correctly. What could be the reason and how to work around it correctly.

IF you are doing what I think you are, you are using an unacceptable argument
for the return_type argument in the WEEKNUM function. Although not entirely
clear in the HELP documentation, the ONLY acceptable arguments are 1 or 2 (or
left blank in which case '1' is understood).

What are you trying to do?
--ron

 

[Rick Rothstein \(MVP - VB\)]

I'm guessing you haven't heard of the "4-day work week" that many companies
have already adopted? One of the methods of implementing this schedule is to
give the workers Monday off in conjunction with the normal weekend.

Rick

 

[Rick Rothstein \(MVP - VB\)]

If you don't mind using a User Defined Function (UDF), then here is a method
that creates the worksheet function you want. Keyin Alt+F11 to go to the VB
editor and click on Insert/Module on the menu bar there. Copy/Paste this
code into the code window that appeared when you did that...

Function WeekNumber(D As Date) As Long
If D = 0 Then
WeekNumber = 0
Else
WeekNumber = DatePart("ww", D, vbTuesday, vbFirstJan1)
End If
End Function

Now, back on your work sheet... assuming, say, C3 contains a date, put this
in any other cell...

=WeekNumber(C3)

and it will display the week number you want given the Tuesday start of week
(using the same start of year that the WEEKNUM function uses).

Rick

 

[Stefi]

You are right, but in this case Excel is behind the developments in the
world. Does Excel2007 have this feature?

Regards,
Stefi

„Rick Rothstein (MVP - VB)†ezt írta: