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. |
|
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|