Week number to date

  • Thread starter Thread starter Tony Ramsden
  • Start date Start date
T

Tony Ramsden

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,

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)
 
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
Click to expand...

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
 
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
 
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 said:
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


Click to expand...
 
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
Click to expand...

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
 
Hi,
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).
Click to expand...

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.
Click to expand...

Depends on the weeknumber scheme.

Regards,

Daniel M.
 
Hi,



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

Given the available information from the OP

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.
Click to expand...

I agree. What does it show?




Depends on the weeknumber scheme.

Regards,
Click to expand...

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
 
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
 
Ron

Given the available information from the OP


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?


I agree. What does it show?
Click to expand...
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
 
Hi,
I agree. What does it show?
Click to expand...

On my handspring visor (Palm compatible OS 3.1H3), week 1 of Year 2006 starts
Monday Jan 2nd.
This is 100% compliant with ISO weeknumbering.

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
Click to expand...

No. That's exactly what ISO strives to avoid : having weeks restarting just
because Jan 1 occured!, splitting weeks in two with odd number of days, as in
the Weeknum() fonction numbering scheme.

All ISO weeks are having 7 days and are starting on a Monday. There are NO
exceptions.
In the ISO weeknumbering sheme, the first week starts on the Monday of the week
that has January 4th in it.

Regards,

Daniel M.
 
Oops!!

That line was should have been erased. It is obviously incorrect as you point
out. But I guess there were enough spaces that it scrolled off the bottom of
my screen and was sent out in error.

A key bit of missing information that does, indeed, support your point.


--ron
 
On my handspring visor (Palm compatible OS 3.1H3), week 1 of Year 2006 starts
Monday Jan 2nd.
This is 100% compliant with ISO weeknumbering.
Click to expand...

That being the case, the proper formula for the OP's purposes would be:


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

and Bob's formula will also need some adjustment.


--ron
 
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
Click to expand...

If your Palm is 100% ISO compliant, as is Daniel M's (see other postings in
this thread), then the proper formula for the Monday of a particular week
number would be:


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

You can either custom format this as you wish, or use a text formula if you
need everything in the same cell:

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


--ron
 
Hi Ron,
That being the case, the proper formula for the OP's purposes would be:
=DATE(Year,1,1)-WEEKDAY(DATE(Year,1,1),3)+7*(Weeknumber-(DAY(DATE(Year,1,1)
-WEEKDAY(DATE(Year,1,1),3)+7*(Weeknumber))>4))
Click to expand...

The formula to retrieve the X Weeknumber Monday of Year YYYY (ISO-compliant) is:

=7*WeekNumber+DATE(YYYY,1,3)-WEEKDAY(DATE(YYYY,1,3))-5

Assuming this formula is in D1,
in D2: =D1+1
and so one for the other 5 days.

Regards,

Daniel M.
 
Tony,

Sorry, only just saw your follow-up.

This was a simple error. I added the week number * 7, but should have added
week number -1 *7.

Here's the formula

=TEXT(DATE(B2,1,1)-WEEKDAY(DATE(B2,1,1))+2+(7*(B3-1)),"dddd dd-mm-yy") & "
to "&TEXT(DATE(B2,1,1)-WEEKDAY(DATE(B2,1,1))+2+(7*(B3-1))+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,

Sorry, only just saw your follow-up.

This was a simple error. I added the week number * 7, but should have added
week number -1 *7.

Here's the formula

=TEXT(DATE(B2,1,1)-WEEKDAY(DATE(B2,1,1))+2+(7*(B3-1)),"dddd dd-mm-yy") & "
to "&TEXT(DATE(B2,1,1)-WEEKDAY(DATE(B2,1,1))+2+(7*(B3-1))+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)
 
Back
Top