|
Convert WEEKNUM(date;2) to text "Monday 25th - Sunday 31st October
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 |
Convert WEEKNUM(date;2) to text "Monday 25th - Sunday 31st October
|
Convert WEEKNUM(date;2) to text "Monday 25th - Sunday 31st Oct
On Thu, 28 Oct 2004 00:05:02 -0700, "Martin Los"
wrote: 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 |
Convert WEEKNUM(date;2) to text "Monday 25th - Sunday 31st Oct
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? "Ron Rosenfeld" wrote: On Thu, 28 Oct 2004 00:05:02 -0700, "Martin Los" wrote: 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 |
| All times are GMT +1. The time now is 03:32 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com