Calculating Week Numbers

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

mlv - 24 Apr 2007 14:47 GMT

Hi

One of the fields in an Excel timesheet I use calculates the current week
number.

The formula is :

=ROUNDUP(((Q9-DATE(YEAR(Q9),1,0))/7),0)

Cell Q9 (referenced by the week number calculation) contains the following
formula that calculates Friday's date for the current week :

=IF((TODAY()-36651)/7-INT((TODAY()-36651)/7)>0,36651+7*INT((TODAY()-36651)/7)+7,TODAY())

The function works OK, except I usually have to tweak the week calculation
formula annually, depending on when the new year starts. Generally changing
ROUNDUP to ROUNDDOWN, or vice versa is all that is needed.

At the moment, this calculation seems to increment to the next week number
on the Sunday of each week, when it in fact should not increment until the
Monday

Is there a more elegant way of accurately calculating the week number
without the need for annual tweaking?

Thanks

Signature

Mike
-Please remove 'safetycatch' from email address before firing off your
reply-

Reply to this Message

Bob Phillips - 24 Apr 2007 15:17 GMT

Look at the WEEKNUM function.

Alternatively, try this in Q9

=TODAY()+6-WEEKDAY(TODAY())-(WEEKDAY(TODAY())=1)*7

Signature

---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

> Hi
>
[quoted text clipped - 22 lines]
>
> Thanks

Reply to this Message

Niek Otten - 24 Apr 2007 17:00 GMT

Or, if you need ISO weeknumbers, look here:

http://msdn2.microsoft.com/en-us/library/bb277364.aspx

Signature

Kind regards,

Niek Otten
Microsoft MVP - Excel

| Look at the WEEKNUM function.
|
[quoted text clipped - 28 lines]
| >
| > Thanks

Reply to this Message

David McRitchie - 25 Apr 2007 03:25 GMT

Some more Week Number documentations
and calculations on Chip Pearson's site,
if they others don't have the method you need.
Week Numbers In Excel
http://www.cpearson.com/excel/weeknum.htm

---
HTH,
David McRitchie, Microsoft MVP - Excel
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

> Or, if you need ISO weeknumbers, look here:
>
[quoted text clipped - 32 lines]
> | >
> | > Thanks

Reply to this Message

Ron de Bruin - 25 Apr 2007 21:03 GMT

The link to Chip's page is also in my MSDN article David and is also on my site

Signature

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm

> Some more Week Number documentations
> and calculations on Chip Pearson's site,
[quoted text clipped - 44 lines]
>> | >
>> | > Thanks

Reply to this Message

Hans Terkelsen - 25 Apr 2007 01:08 GMT

> Hi
>
[quoted text clipped - 26 lines]
> -Please remove 'safetycatch' from email address before firing off your
> reply-

Hi Mike.

In UK I suppose thay you use european, ISO, weeknumbers.
Those that start monday, and are all 7 days.
And monday of week 1 lies between 29dec and 4jan.

Friday of current week could be
=FLOOR(TODAY()+5,7)-1

There are many ways to get the european weeknumber.
How about this (date in A1):

1/1/1900-28/12/2104:
=INT(MOD(INT((A1+2924)/7)*28,1461)/28+1)

Or an UDF:

Function WkIso(d) '..1/1/100-31/12/9999..
WkIso = ((((d + 692501) \ 7 Mod 20871) * 28 + 4383) Mod 146096 Mod 1461) \ 28 + 1
End Function

The last one in Excel would be

Valid for all dates:
=INT(MOD(MOD(MOD(INT((A1+692501)/7),20871)*28+4383,146096),1461)/28+1)

Using only elementary functions, MOD and INT, makes this method fast and versatile.

Hope you can use that, Hans.

Reply to this Message

Hans Terkelsen - 25 Apr 2007 04:01 GMT

> Hi
>
[quoted text clipped - 26 lines]
> -Please remove 'safetycatch' from email address before firing off your
> reply-

Hi Mike.

Something happened to my first reply, some hours ago,
so this is just to repeat:

In UK I suppose that you use european, ISO, weeknumbers.
Those that start monday, and are all 7 days.
And monday of week 1 lies between 29dec and 4jan.

Friday of the current week could be
=FLOOR(TODAY()+5,7)-1

You may have to use ; instead of , as a separator.

There are many ways to get the european weeknumber.
How about this (date in A1):

Valid 1/1/1900-28/12/2104:
=INT(MOD(INT((A1+2924)/7)*28,1461)/28+1)

Or an UDF:

Function WkIso(d) '..1/1/100-31/12/9999..
WkIso = ((((d + 692501) \ 7 Mod 20871) * 28 + 4383) Mod 146096 Mod 1461) \ 28 + 1
End Function

The last calculation, done in Excel, is unnescessarily long perhaps,
but it is valid for all Excel-dates:

=INT(MOD(MOD(MOD(INT((A1+692501)/7),20871)*28+4383,146096),1461)/28+1)

Using only elementary functions, MOD and INT, makes this method fast and versatile.

Hope you can use it, Hans.

Reply to this Message

mlv - 25 Apr 2007 18:16 GMT

> In UK I suppose that you use european, ISO, weeknumbers.
> Those that start monday, and are all 7 days.
[quoted text clipped - 25 lines]
> Using only elementary functions, MOD and INT, makes this method fast and
> versatile.

Hi Hans

Thanks for the information. So many ways of achieving the same answer!

I also found:

=1+INT((A1-DATE(YEAR(A1+4-WEEKDAY(A1+6)),1,5)+WEEKDAY(DATE(YEAR(A1+4-WEEKDAY(A1+6)),1,3)))/7)

for calculating the ISO week number.

I like the simplicity of =FLOOR(TODAY()+5,7)-1 for finding the current
week's Friday.

Is =FLOOR(TODAY()+5,7)-5 and

=FLOOR(TODAY()+5,7)

correct for respectively finding the current week's Monday and Saturday, or
have I misunderstood how the formula works?

I would also like to automatically enter the following information into one
cell (i.e. for today's date):

Today is Wednesday, 25 April 2007

I guess I would start:

"Today is " & text(A1)...

but I'm not sure how to obtain 'Wednesday,' or how to ensure the date is
formatted as '25 April 2007' in the text string.

Can you (or anyone else) help?

Thanks

Signature

Mike
-Please remove 'safetycatch' from email address before firing off your
reply-

Reply to this Message

Roger Govier - 25 Apr 2007 22:33 GMT

Hi Mike

="Today is " & text(A1,"dddd, dd mmmm yyyy")

Signature

Regards

Roger Govier

>> In UK I suppose that you use european, ISO, weeknumbers.
>> Those that start monday, and are all 7 days.
[quoted text clipped - 62 lines]
>
> Thanks

Reply to this Message

Hans Terkelsen - 26 Apr 2007 08:10 GMT

...

> Hi Hans
>
[quoted text clipped - 15 lines]
> correct for respectively finding the current week's Monday and Saturday, or
> have I misunderstood how the formula works?

Hi Mike!

About the FLOOR function:

=FLOOR(A1,7) floors on the Saturdays.

With a list of dates in A:A
and =FLOOR(A1+$C$1,7)+$D$1 i B1 and down, suitably formatted,
you can vary C1 and D1 to see which numbers are required
for the patterns you want.

Best wishes Hans.

Reply to this Message

mlv - 27 Apr 2007 13:25 GMT

Excellent ng!

Thanks for all the helpful answers.

Signature

Mike
-Please remove 'safetycatch' from email address before firing off your
reply-

Reply to this Message