week numbers

[Kevin Carter]

Hi
I am trying to insert the week numbers
i would like the week to start on a Saturday and end on the Friday
i have tried this formula
=TRUNC(((StartDate-DATE(YEAR(StartDate),1,1)+
MOD(DayOfWeek-WEEKDAY(DATE(YEAR(StartDate),1,1)),7))+6)/7)
where StartDate is the whose week number is to be found, and DayOfWeek is the day of week number (1 = Sunday, 2 = Monday, ..., 7 = Saturday).

my formula :-
=TRUNC(((B21-DATE(YEAR(B21),1,1)+MOD(7-WEEKDAY(DATE(YEAR(B21),1,1)),7))+6)/7)
on chip pearsons web site
what i am finding when i get to Friday of the first week and the week number changes to 2, later in the year (17-1-12)a Tuesday the week number changes to 4 which should be week 3

any ideas how i can resolve this

thanks

kevin

 

[Ron Rosenfeld]

Hi
I am trying to insert the week numbers
i would like the week to start on a Saturday and end on the Friday
i have tried this formula
=TRUNC(((StartDate-DATE(YEAR(StartDate),1,1)+
MOD(DayOfWeek-WEEKDAY(DATE(YEAR(StartDate),1,1)),7))+6)/7)
where StartDate is the whose week number is to be found, and DayOfWeek is the day of week number (1 = Sunday, 2 = Monday, ..., 7 = Saturday).

my formula :-
=TRUNC(((B21-DATE(YEAR(B21),1,1)+MOD(7-WEEKDAY(DATE(YEAR(B21),1,1)),7))+6)/7)
on chip pearsons web site
what i am finding when i get to Friday of the first week and the week number changes to 2, later in the year (17-1-12)a Tuesday the week number changes to 4 which should be week 3

any ideas how i can resolve this

thanks

kevin

In your system, how do you define the first week (week 1) of the year?

 

[Kevin Carter]

What do you mean when you say system?
if you are refering to the spreadsheet i have entered the date ie 1/1/2011

 

[Ron Rosenfeld]

What do you mean when you say system?
if you are refering to the spreadsheet i have entered the date ie 1/1/2011

You need to have some kind of rule for determining the first week in addition to "Week starts on Saturday" for those years when January 1 is not on a Saturday.

For example, if in your system, January 1 will always be in the first week of the year, then Week 1 might include a few days of the previous year.
On the other hand, if you decide that the Week 1 will always start on the first Saturday of the year, then there may be a few days in January that are in Week 53 of the previous year.

Or you may have some other system.

The proper formula to determine the weeknumber will be different in those instances.

 

[Kevin Carter]

thanks for reply
i can see where your coming from (i didn't think of that)
reading your two examples i my case it will be the first full week back
for example this year 1/1/2011 was a Saturday we started work monday 3
next year we start on the 3rd so the 31/12/ would be the start of the week

kevin

 

[Ron Rosenfeld]

thanks for reply
i can see where your coming from (i didn't think of that)
reading your two examples i my case it will be the first full week back
for example this year 1/1/2011 was a Saturday we started work monday 3
next year we start on the 3rd so the 31/12/ would be the start of the week

kevin

Ok, so the system you want to use, so far, is:

1. Saturday is Day 1 of the week.
2. Week 1 of the year is the Saturday of the week that includes Jan 1
(so it can start on or before Jan 1, but never after).

With your date whose weeknumber you want to determine in A1, we determine the weeknumber by
1. Compute First Saturday of the year.
2. Subtract First Saturday from the date in A1
3. Divide by 7
4. Truncate the number, or take the Integer portion.
5. That will give a zero-based result, so we then add 1

The formula for that is:

=INT((A1-DATE(YEAR(A1),1,2)+WEEKDAY(DATE(YEAR(A1),1,2)))/7)+1

We still have one more issue you need to clarify with regard to computing your week number.
Take the year 2009 where 1 Jan is on a Thursday.

Do we consider that 30 December is in Week 52 of 2008, obviously we would have two "short" weeks, or does Week 1 of 2009 include 12/30/2008?
The above formula will return 52 for 30 December 2008 and 1 for 1 Jan 2009.

 

[Kevin Carter]

fantastic thank you very much for your time
kevin

 

[Ron Rosenfeld]

fantastic thank you very much for your time
kevin

Glad to help. Thanks for the feedback. I guess you are going with the short week for the last/first weeks of the year.