Calculating Week Numbers

[mlv]

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

 

[Bob Phillips]

Look at the WEEKNUM function.

Alternatively, try this in Q9

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

--
---
HTH

Bob

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

 

[Niek Otten]

Or, if you need ISO weeknumbers, look here:

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

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| Look at the WEEKNUM function.
|
| Alternatively, try this in Q9
|
| =TODAY()+6-WEEKDAY(TODAY())-(WEEKDAY(TODAY())=1)*7
|
| --
| ---
| HTH
|
| Bob
|
| (there's no email, no snail mail, but somewhere should be gmail in my addy)
|
|
|
| | > 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
| > --
| > Mike
| > -Please remove 'safetycatch' from email address before firing off your
| > reply-
| >
|
|

 

[Hans Terkelsen]

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

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.

 

[David McRitchie]

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

 

[Hans Terkelsen]

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

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.

 

[mlv]

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.

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

 

[Ron de Bruin]

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

 

[Roger Govier]

Hi Mike

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

 

[Hans Terkelsen]

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?

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.

 

[mlv]

Excellent ng!

Thanks for all the helpful answers.