Convert WEEKNUM(date;2) to text "Monday 25th - Sunday 31st October

[Martin Los]

Two questions:

1. "Get text string on basis of date":
In cell A1 I have a date with format "dd/mm/yyyy" (for example
"27/10/2004"). Now I want to get in cell B1 a text string telling me the week
that date falls into (for example "Monday 25th - Sunday 31st October").

What function do I have to use in cell B2 to get this text string?

2. "Get text string on basis of weeknumber":
In cell A1 I have the number of a week (WEEKNUM(A1;2)). How can I get in B1
a text string telling me the week that date falls into (for example "Monday
25th - Sunday 31st October") using the value of the weeknumber in A1?

TIA

Martin

 

[Ron Rosenfeld]

On Wed, 27 Oct 2004 06:01:03 -0700, "Martin Los" <Martin

Two questions:

1. "Get text string on basis of date":
In cell A1 I have a date with format "dd/mm/yyyy" (for example
"27/10/2004"). Now I want to get in cell B1 a text string telling me the week
that date falls into (for example "Monday 25th - Sunday 31st October").

What function do I have to use in cell B2 to get this text string?

=TEXT(A1+1-WEEKDAY(A1-1),"dddd dd") & " - "
& TEXT(A1+7-WEEKDAY(A1-1),"dddd dd mmmm")

(You will probably want to change my commas (,) into semicolons (.

2. "Get text string on basis of weeknumber":
In cell A1 I have the number of a week (WEEKNUM(A1;2)). How can I get in B1
a text string telling me the week that date falls into (for example "Monday
25th - Sunday 31st October") using the value of the weeknumber in A1?

Consider if you want to use Excel's WEEKNUM function, or if you want to compute
the ISO Weeknumber which is defined differently.


--ron

 

[Martin Los]

Dear Ron:

Your first answer works great for dates.

Concerning the second question, my boss asks me to give weekly reports,
running from monday to sunday. That´s why I need a function to transfer
weeknumbers into text strings like "Monday 1st - Sunday 7th November".

www.cpearsob.com/excel/weeknum.htm gives an example how to calculate the
weeknumber based on a date.

I want to do the other way around: calculate the week textstring based on a
weeknumber.
Any ideas how to get this function working? (Let´s stick with the
weeknum(date,2) format ok?)

 

[Ron Rosenfeld]

Dear Ron:

Your first answer works great for dates.

Concerning the second question, my boss asks me to give weekly reports,
running from monday to sunday. That´s why I need a function to transfer
weeknumbers into text strings like "Monday 1st - Sunday 7th November".

www.cpearsob.com/excel/weeknum.htm gives an example how to calculate the
weeknumber based on a date.

I want to do the other way around: calculate the week textstring based on a
weeknumber.
Any ideas how to get this function working? (Let´s stick with the
weeknum(date,2) format ok?)

The problem with WEEKNUM is that you have to decide what you want to do with
"weeks" that are less than seven days long. Week 1 will start on January 1,
regardless of the day of the week; and Week 2 will start on the next Monday.
Week 53 will end on the last day of the year.

For example, using the formula =WEEKNUM(dt;2), and starting with 27 Dec 2004,
we have week 53 which is five days long, and then we have week 1 of 2005 which
is 2 days long.

A WEEK defined according to the ISO definition will always be seven days long,
and will always start on a Monday.

So you need to check with your boss as to what he wants to do with "weekly"
reports when the last/first week of the year is not a full week.


--ron

 

[Orcababe]

I also need to work out how to do No 2, i. e Covert WEEKNUM(date;2) to text
"Monday 25th - SUnday 31st Oct and defintely by WEEKNUM date and not ISO
definition. Basically I have a pivot table that counts how many patients in
that particular week (ending Sundays) and need to put the dates above of what
what week number is. Can anyone help?