Week number to date

On Mon, 13 Oct 2003 00:23:12 -0400, "Daniel.M"
wrote:

Wouldn't a "perfectly compliant ISO weeknumber scheme" start with January 1?
--ron

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.

On my handspring visor (Palm compatible OS 3.1H3), week 1 of Year 2006 starts
Monday Jan 2nd.

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


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

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.

On Mon, 13 Oct 2003 09:57:40 -0400, "Daniel.M"
wrote:

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

Nice


--ron

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 Ramsden" wrote in message
...
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")

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 Ramsden" wrote in message
...
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")