Weeknum issue

[Don Brownlee]

I need to sort/subtotal/analyze data based on week of the year. I'm familiar with the WEEKNUM function...but I need to sort etc by a Work Week that begins on Saturday. The options for WEEKNUM seem to be a week that begins on Sunday (1) or Monday (2). Can anyone offer some help on how to sort etc by a Work Week that begins on Saturday?

Using an IF Statement seems to get the result I want:
=IF(WEEKDAY(A1)=7,(WEEKNUM(A1)+1),WEEKNUM(A1))

but was hoping to go through fewer wickets.

thanks

Don

[tfspry]

Hello - Can you use this?
WEEKNUM(A1,7)

If that does not work, can you attach a sample?

Tim

FYI ......

Syntax

WEEKNUM(serial_num,return_type)

Serial_num is a date within the week. Dates should be entered by using the DATE function, or as results of other formulas or functions. For example, use DATE(2008,5,23) for the 23rd day of May, 2008. Problems can occur if dates are entered as text.

Return_type is a number that determines on which day the week begins. The default is 1.

Return_type Week Begins
1 Week begins on Sunday. Weekdays are numbered 1 through 7.
2 Week begins on Monday. Weekdays are numbered 1 through 7.

[ralphk]

Hi Don,

Not sure if this works in previous versions, but the description below is from the Help file in Excel 2010. You should be able to use WEEKNUM(A1, 16) to get the weeks starting on a Saturday.

Hope this helps.

========

Description
Returns the week number of a specific date. For example, the week containing January 1 is the first week of the year, and is numbered week 1.

There are two systems used for this function:

System 1 The week containing January 1 is the first week of the year, and is numbered week 1.
System 2 The week containing the first Thursday of the year is the first week of the year, and is numbered as week 1.

Syntax
WEEKNUM(serial_number,[return_type])
The WEEKNUM function syntax has the following arguments (argument: A value that provides information to an action, an event, a method, a property, a function, or a procedure.):

Serial_number Required. A date within the week. Dates should be entered by using the DATE function, or as results of other formulas or functions. For example, use DATE(2008,5,23) for the 23rd day of May, 2008. Problems can occur if dates are entered as text.

Return_type Optional. A number that determines on which day the week begins. The default is 1.

Return_type Week begins on System
1 or omitted Sunday 1
2 Monday 1
11 Monday 1
12 Tuesday 1
13 Wednesday 1
14 Thursday 1
15 Friday 1
16 Saturday 1
17 Sunday 1
21 Monday 2