A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Week number to date



 
 
Thread Tools Display Modes
  #1  
Old October 10th, 2003, 10:29 PM
Tony Ramsden
external usenet poster
 
Posts: n/a
Default Week number to date

How do I make my excel sheet indicate the actual start and finish
dates of a working week when the week number is inserted,( It would
also be nice to do the opposite and give a date and have the week
number shown but not imperative).. So

if I input the week number in B3
and hey presto..
The week dates shown in say C3 (& C4-C7 if necessary ), in the format
of
Monday dd-mm-yy To Sunday dd-mm-yy

Many thanks

Tony
  #2  
Old October 10th, 2003, 10:43 PM
Bob Phillips
external usenet poster
 
Posts: n/a
Default Week number to date

Tony,

a bit long-winded, but very straight-forward

=TEXT(DATE(YEAR(TODAY()),1,1)-WEEKDAY(DATE(YEAR(TODAY()),1,1))+2+(7*B3),
"dddd dd-mm-yy") & " to " &
TEXT(DATE(YEAR(TODAY()),1,1)-WEEKDAY(DATE(YEAR(TODAY()),1,1))+2+(7*B3)+6,
"dddd dd-mm-yy")

You could store the repeated expressions in another cell to simplify it.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Tony Ramsden" wrote in message
...
How do I make my excel sheet indicate the actual start and finish
dates of a working week when the week number is inserted,( It would
also be nice to do the opposite and give a date and have the week
number shown but not imperative).. So

if I input the week number in B3
and hey presto..
The week dates shown in say C3 (& C4-C7 if necessary ), in the format
of
Monday dd-mm-yy To Sunday dd-mm-yy

Many thanks

Tony



  #3  
Old October 11th, 2003, 03:18 AM
Ron Rosenfeld
external usenet poster
 
Posts: n/a
Default Week number to date

On Fri, 10 Oct 2003 22:29:08 +0100, Tony Ramsden wrote:

How do I make my excel sheet indicate the actual start and finish
dates of a working week when the week number is inserted,( It would
also be nice to do the opposite and give a date and have the week
number shown but not imperative).. So

if I input the week number in B3
and hey presto..
The week dates shown in say C3 (& C4-C7 if necessary ), in the format
of
Monday dd-mm-yy To Sunday dd-mm-yy

Many thanks

Tony


Tony,

Your question lacks information as to how you define Week Number.

There are a variety of definitions available. For example, the ISO definition
states that a week always begins on a Monday, and Week 1 always contains the
first Thursday of the year (or January 4th). So the first week might have as
few as four days, and the first day or two of one year might really fall in the
last week of the preceding year.

You could define Week 1 as always starting on Jan 1, without regard to the day
of the week.

Or you could use the definitions implied by the Excel ATP WEEKNUM function.

So to accurately answer your question, you need to define your week number.




--ron
  #4  
Old October 11th, 2003, 10:23 AM
Tony Ramsden
external usenet poster
 
Posts: n/a
Default Week number to date

Ron

I want to use the same system that my palm uses, the idea behind the
spreansheet is to contain oncall info for work , I shall mainly be
using my Palm Tunsten C at work, but with access to my home PC aswell
as work PC's. I can simply reach for the Palm to tell colleagues when
their Oncall is.
The Palm's Week 1 for this year is Mon 30 dec 2002 to Sunday 5 Jan
2003.

Bob Phillips method works great for this year. One question about his
method is how do I work out the dates for say week 5 2004...


Thanks for all replies

Tony


On Fri, 10 Oct 2003 22:18:24 -0400, Ron Rosenfeld
wrote:

On Fri, 10 Oct 2003 22:29:08 +0100, Tony Ramsden wrote:

How do I make my excel sheet indicate the actual start and finish
dates of a working week when the week number is inserted,( It would
also be nice to do the opposite and give a date and have the week
number shown but not imperative).. So

if I input the week number in B3
and hey presto..
The week dates shown in say C3 (& C4-C7 if necessary ), in the format
of
Monday dd-mm-yy To Sunday dd-mm-yy

Many thanks

Tony


Tony,

Your question lacks information as to how you define Week Number.

There are a variety of definitions available. For example, the ISO definition
states that a week always begins on a Monday, and Week 1 always contains the
first Thursday of the year (or January 4th). So the first week might have as
few as four days, and the first day or two of one year might really fall in the
last week of the preceding year.

You could define Week 1 as always starting on Jan 1, without regard to the day
of the week.

Or you could use the definitions implied by the Excel ATP WEEKNUM function.

So to accurately answer your question, you need to define your week number.




--ron


  #5  
Old October 11th, 2003, 11:17 AM
Bob Phillips
external usenet poster
 
Posts: n/a
Default Week number to date

Tony,

If you put the year number in say B2 as well as the week in B3, this works
for all years

=TEXT(DATE(B2,1,1)-WEEKDAY(DATE(B2,1,1))+2+(7*B3),"dddd dd-mm-yy") & " to "
&TEXT(DATE(B2,1,1)-WEEKDAY(DATE(B2,1,1))+2+(7*B3)+6,"dddd dd-mm-yy")

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Tony Ramsden" wrote in message
...
Ron

I want to use the same system that my palm uses, the idea behind the
spreansheet is to contain oncall info for work , I shall mainly be
using my Palm Tunsten C at work, but with access to my home PC aswell
as work PC's. I can simply reach for the Palm to tell colleagues when
their Oncall is.
The Palm's Week 1 for this year is Mon 30 dec 2002 to Sunday 5 Jan
2003.

Bob Phillips method works great for this year. One question about his
method is how do I work out the dates for say week 5 2004...


Thanks for all replies

Tony


On Fri, 10 Oct 2003 22:18:24 -0400, Ron Rosenfeld
wrote:

On Fri, 10 Oct 2003 22:29:08 +0100, Tony Ramsden

wrote:

How do I make my excel sheet indicate the actual start and finish
dates of a working week when the week number is inserted,( It would
also be nice to do the opposite and give a date and have the week
number shown but not imperative).. So

if I input the week number in B3
and hey presto..
The week dates shown in say C3 (& C4-C7 if necessary ), in the format
of
Monday dd-mm-yy To Sunday dd-mm-yy

Many thanks

Tony


Tony,

Your question lacks information as to how you define Week Number.

There are a variety of definitions available. For example, the ISO

definition
states that a week always begins on a Monday, and Week 1 always contains

the
first Thursday of the year (or January 4th). So the first week might

have as
few as four days, and the first day or two of one year might really fall

in the
last week of the preceding year.

You could define Week 1 as always starting on Jan 1, without regard to

the day
of the week.

Or you could use the definitions implied by the Excel ATP WEEKNUM

function.

So to accurately answer your question, you need to define your week

number.




--ron




  #6  
Old October 11th, 2003, 01:23 PM
Ron Rosenfeld
external usenet poster
 
Posts: n/a
Default Week number to date

On Sat, 11 Oct 2003 10:23:12 +0100, Tony Ramsden wrote:

I want to use the same system that my palm uses, the idea behind the
spreansheet is to contain oncall info for work , I shall mainly be
using my Palm Tunsten C at work, but with access to my home PC aswell
as work PC's. I can simply reach for the Palm to tell colleagues when
their Oncall is.
The Palm's Week 1 for this year is Mon 30 dec 2002 to Sunday 5 Jan
2003.

Bob Phillips method works great for this year. One question about his
method is how do I work out the dates for say week 5 2004...


Thanks for all replies


Well, if Bob's method works (and he posted something to answer your question),
then I guess you are home free.

It sounds like the Palm must label week one as starting on the Monday of the
week that includes Jan 1 (even if that Monday is from the previous year). That
being the case, the 53rd week of one year might be identical to the 1st week of
the subsequent year.

That's OK so long as it works for you.

That being the case, I believe this formual will also work for you:

=DATE(Year,1,1)-WEEKDAY(DATE(Year,1,1),3)+7*(Weeknumber-1)

Where Year is the year of interest, and Weeknumber is the Weeknumber of
interest.

The above formula will give the Monday of the desired week.

To get the rest of the days, merely add 1 for each day.

So, for example, if the above formula is in C3, in C4 enter the formula:

=C3+1

and drag down through C7 to get Mon-Fri; or C9 to get Mon-Sun

If you want the result to be in a single cell as text representing a range,
then something like:

=TEXT(DATE(Year,1,1)-WEEKDAY(DATE(Year,1,1),3)+7*(Weeknumber-1),"ddd dd-mm-yy")
&" to "&
TEXT(DATE(Year,1,1)-WEEKDAY(DATE(Year,1,1),3)+7*(Weeknumber-1)+7,"ddd
dd-mm-yy")

should give that result.






--ron
  #7  
Old October 12th, 2003, 04:00 PM
Daniel.M
external usenet poster
 
Posts: n/a
Default Week number to date

Hi,

The Palm's Week 1 for this year is Mon 30 dec 2002 to Sunday 5 Jan
2003.


It sounds like the Palm must label week one as starting on
the Monday of the week that includes Jan 1 (even if that
Monday is from the previous year).


AFAIK, it seems to be perfectly compliant to the ISO weeknumber scheme.
Year 2006 would be the crucial test here.
If week 1 starts with Jan 2nd, it is still ISO compliant (and not compliant with
your definition), first Monday is Monday of week containing Jan 4th.


That being the case, the 53rd week of one year might be
identical to the 1st week of the subsequent year.


Depends on the weeknumber scheme.

Regards,

Daniel M.


  #8  
Old October 12th, 2003, 08:17 PM
Ron Rosenfeld
external usenet poster
 
Posts: n/a
Default Week number to date

On Sun, 12 Oct 2003 11:00:09 -0400, "Daniel.M"
wrote:

Hi,

The Palm's Week 1 for this year is Mon 30 dec 2002 to Sunday 5 Jan
2003.


It sounds like the Palm must label week one as starting on
the Monday of the week that includes Jan 1 (even if that
Monday is from the previous year).


AFAIK, it seems to be perfectly compliant to the ISO weeknumber scheme.


Given the available information from the OP

"The Palm's Week 1 for this year is Mon 30 dec 2002 to Sunday 5 Jan 2003"


it could be perfectly compliant with any number of weeknumbering schemes.
Perhaps I am missing a message or two which has more information about the
Palm?

Year 2006 would be the crucial test here.


I agree. What does it show?







That being the case, the 53rd week of one year might be
identical to the 1st week of the subsequent year.


Depends on the weeknumber scheme.

Regards,


The OP stated "The Palm's Week 1 for this year is Mon 30 dec 2002 to Sunday 5
Jan 2003".

Wouldn't a "perfectly compliant ISO weeknumber scheme" start with January 1?
--ron
  #9  
Old October 12th, 2003, 10:07 PM
Tony Ramsden
external usenet poster
 
Posts: n/a
Default Week number to date

Thanks again bob

The weeks are out of sync with my palm by one week,
eg. Tommorow Monday 13 oct is week 42 according to palm, but according
to your calculations its week 41, I suppose its only a small tweek for
a man of your talents... so make it simple for me..

Thanks again


Tony



On Sat, 11 Oct 2003 11:17:20 +0100, "Bob Phillips"
wrote:

Tony,

If you put the year number in say B2 as well as the week in B3, this works
for all years

=TEXT(DATE(B2,1,1)-WEEKDAY(DATE(B2,1,1))+2+(7*B3),"dddd dd-mm-yy") & " to "
&TEXT(DATE(B2,1,1)-WEEKDAY(DATE(B2,1,1))+2+(7*B3)+6,"dddd dd-mm-yy")


  #10  
Old October 12th, 2003, 10:15 PM
Tony Ramsden
external usenet poster
 
Posts: n/a
Default Week number to date


Ron

On Sun, 12 Oct 2003 15:17:43 -0400, Ron Rosenfeld
wrote:

On Sun, 12 Oct 2003 11:00:09 -0400, "Daniel.M"
wrote:

Hi,

The Palm's Week 1 for this year is Mon 30 dec 2002 to Sunday 5 Jan
2003.


It sounds like the Palm must label week one as starting on
the Monday of the week that includes Jan 1 (even if that
Monday is from the previous year).


AFAIK, it seems to be perfectly compliant to the ISO weeknumber scheme.


Given the available information from the OP

"The Palm's Week 1 for this year is Mon 30 dec 2002 to Sunday 5 Jan 2003"


it could be perfectly compliant with any number of weeknumbering schemes.
Perhaps I am missing a message or two which has more information about the
Palm?

Year 2006 would be the crucial test here.


I agree. What does it show?

My palm shows week one 2006 to be Monday 2nd Jan to Sunday 8th Jan.

Interesting !!!

Perhaps the palm OS uses the first week which includes jan 1st , and
internally includes sunday as its first week day, I remember in the
setup of the diary indicating that I wanted the working week to start
on a Monday... and for the purpose of the spreadsheet I still do.


Tony
 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 03:27 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.