date filtering by number

[Lynn]

I have a spreadsheet that lists, with start dates in column C. I would
like to be able to automatically number the dates which fall within
the same week

example, if start date falls within this week, column D will be
labelled as 1. if start date falls within next week, column D will be
labelled as 2. if start date falls within 3rd week, column D will be
labelled as 3.... and so on...

Can anyone clue me in as to how to do this?

 

[Lars-Åke Aspelin]

I have a spreadsheet that lists, with start dates in column C. I would
like to be able to automatically number the dates which fall within
the same week

example, if start date falls within this week, column D will be
labelled as 1. if start date falls within next week, column D will be
labelled as 2. if start date falls within 3rd week, column D will be
labelled as 3.... and so on...

Can anyone clue me in as to how to do this?

Assuming that a week starts on a Monday, try this formula in cell D1:

=1+INT((C1-TODAY()+WEEKDAY(TODAY(),3))/7)

Hope this helps / Lars-Åke

 

[john]

You can return the week number of a date we can use the WEEKNUM formula.
This will return a number that indicates where the week falls numerically
within a year.
If this function is not available, and returns the #NAME? error, install and
load the Analysis ToolPak add-in. – from your menu bar - Tools>Add-ins.

Formula syntax for the WEEKNUM formula:-

WEEKNUM(serial_num,return_type)

Serial_num is a valid date.
Return_type is a number that determines the day the week begins.

- Return_type 1 Default, (it can be omitted) the week begins on a Sunday.

- Return_type 2, the week begins on a Monday.

example:

=WEEKNUM(C1) or =WEEKNUM(C1,2)


hope helpful