Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to display date as week number
Hi
Is is possible to display a date (2006.11.19) as week number (47)? I have tried to find a usefull format in format cells..., but it seems that is not possible - no "ww" in there... Any help would be appriciated. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to display date as week number
Steen
See http://www.rondebruin.nl/weeknumber.htm and http://www.rondebruin.nl/isodate.htm HTH Mike Rogers "Steen" wrote: Hi Is is possible to display a date (2006.11.19) as week number (47)? I have tried to find a usefull format in format cells..., but it seems that is not possible - no "ww" in there... Any help would be appriciated. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to display date as week number
No it is not. You can get the week number in an adjacent cell, but there is
no weeknumber format. -- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) "Steen" wrote in message ... Hi Is is possible to display a date (2006.11.19) as week number (47)? I have tried to find a usefull format in format cells..., but it seems that is not possible - no "ww" in there... Any help would be appriciated. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to display date as week number
WEEKNUM
See Also Returns a number that indicates where the week falls numerically within a year. If this function is not available, and returns the #NAME? error, install and load the Analysis ToolPak add-in. How? On the Tools menu, click Add-Ins. In the Add-Ins available list, select the Analysis ToolPak box, and then click OK. If necessary, follow the instructions in the setup program. Syntax WEEKNUM(serial_num,return_type) Serial_num is a date within the week. Dates should be entered by using the DATE function, or as results of other formulas or functions. For example, use DATE(2008,5,23) for the 23rd day of May, 2008. Problems can occur if dates are entered as text. Return_type is a number that determines on which day the week begins. The default is 1. Return_type Week Begins 1 Week begins on Sunday. Weekdays are numbered 1 through 7. 2 Week begins on Monday. Weekdays are numbered 1 through 7. -- paul remove nospam for email addy! "Steen" wrote: Hi Is is possible to display a date (2006.11.19) as week number (47)? I have tried to find a usefull format in format cells..., but it seems that is not possible - no "ww" in there... Any help would be appriciated. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to display date as week number
You could always use a formula like this with your date in A1.
=ROUNDUP((A1-DATE(YEAR(A1),1,0))/7,0) Hope it helps... "Steen" wrote: Hi Is is possible to display a date (2006.11.19) as week number (47)? I have tried to find a usefull format in format cells..., but it seems that is not possible - no "ww" in there... Any help would be appriciated. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to display date as week number
There was a link to this a few days back.
Week number is defined in some special ISO standard Steve On Sun, 19 Nov 2006 08:07:01 -0000, OC wrote: You could always use a formula like this with your date in A1. =ROUNDUP((A1-DATE(YEAR(A1),1,0))/7,0) Hope it helps... "Steen" wrote: Hi Is is possible to display a date (2006.11.19) as week number (47)? I have tried to find a usefull format in format cells..., but it seems that is not possible - no "ww" in there... Any help would be appriciated. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to display date as week number
Hi Bob and all you others that have been kindly to invest time in this issus.
I almost new it - it's not possible to do. Could it then be done the other way arround? I am using week number as deadline in a col and status in another col and would like to give a color (red) to deadlines that are after due date (read week) which dont have the status closed. (if closed whitin do date color green). Can this be done? "Bob Phillips" wrote: No it is not. You can get the week number in an adjacent cell, but there is no weeknumber format. -- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) "Steen" wrote in message ... Hi Is is possible to display a date (2006.11.19) as week number (47)? I have tried to find a usefull format in format cells..., but it seems that is not possible - no "ww" in there... Any help would be appriciated. |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to display date as week number
Hi All
Thanks again for all your answers - I know that the use of WEEKNUM would solve the problem quite easy, but this solution needs an extra col. which I don't want. Could there be anoter way to accopblish this for exampel by changing a deadline weeknumber (ex. 46) into a date that could be hidden in a nother col. Then I could use this hidden column to check if its after deadling and use conditional formatting to change format according to this? /Steen "SteveW" wrote: There was a link to this a few days back. Week number is defined in some special ISO standard Steve On Sun, 19 Nov 2006 08:07:01 -0000, OC wrote: You could always use a formula like this with your date in A1. =ROUNDUP((A1-DATE(YEAR(A1),1,0))/7,0) Hope it helps... "Steen" wrote: Hi Is is possible to display a date (2006.11.19) as week number (47)? I have tried to find a usefull format in format cells..., but it seems that is not possible - no "ww" in there... Any help would be appriciated. |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to display date as week number
Yes, you could use conditional formatting.
Let's start by assuming the weeknum is in column A, and status is in column B, and the data starts in row 2. Select all of the rows in column A and B that you want to test. Goto FormatConditional Formatting Change Condition 1 to Formula Is Add a formula of =AND($A220,$B2<"Closed") Click the format button Select Pattern tab Choose a colour OK OK This tests for later than week 20 -- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) "Steen" wrote in message ... Hi Bob and all you others that have been kindly to invest time in this issus. I almost new it - it's not possible to do. Could it then be done the other way arround? I am using week number as deadline in a col and status in another col and would like to give a color (red) to deadlines that are after due date (read week) which dont have the status closed. (if closed whitin do date color green). Can this be done? "Bob Phillips" wrote: No it is not. You can get the week number in an adjacent cell, but there is no weeknumber format. -- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) "Steen" wrote in message ... Hi Is is possible to display a date (2006.11.19) as week number (47)? I have tried to find a usefull format in format cells..., but it seems that is not possible - no "ww" in there... Any help would be appriciated. |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to display date as week number
Hi Bob
That works nicely - thank you. All how there is still a problem conserning the year end where weeknumber goes from 52 to 1. For now I have solved this using dates instead of weeks (I only have one colum for deadline) and setup a conditional formular like the one you suggested: =($A2<Today(),$B2<"Closed") where Col A holds the deadlines. It still would be nice to use week number for input and display of deadlines... "Bob Phillips" wrote: Yes, you could use conditional formatting. Let's start by assuming the weeknum is in column A, and status is in column B, and the data starts in row 2. Select all of the rows in column A and B that you want to test. Goto FormatConditional Formatting Change Condition 1 to Formula Is Add a formula of =AND($A220,$B2<"Closed") Click the format button Select Pattern tab Choose a colour OK OK This tests for later than week 20 -- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) "Steen" wrote in message ... Hi Bob and all you others that have been kindly to invest time in this issus. I almost new it - it's not possible to do. Could it then be done the other way arround? I am using week number as deadline in a col and status in another col and would like to give a color (red) to deadlines that are after due date (read week) which dont have the status closed. (if closed whitin do date color green). Can this be done? "Bob Phillips" wrote: No it is not. You can get the week number in an adjacent cell, but there is no weeknumber format. -- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) "Steen" wrote in message ... Hi Is is possible to display a date (2006.11.19) as week number (47)? I have tried to find a usefull format in format cells..., but it seems that is not possible - no "ww" in there... Any help would be appriciated. |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to display date as week number
Why don't you use year and week number?
-- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) "Bob Phillips" wrote in message ... Yes, you could use conditional formatting. Let's start by assuming the weeknum is in column A, and status is in column B, and the data starts in row 2. Select all of the rows in column A and B that you want to test. Goto FormatConditional Formatting Change Condition 1 to Formula Is Add a formula of =AND($A220,$B2<"Closed") Click the format button Select Pattern tab Choose a colour OK OK This tests for later than week 20 -- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) "Steen" wrote in message ... Hi Bob and all you others that have been kindly to invest time in this issus. I almost new it - it's not possible to do. Could it then be done the other way arround? I am using week number as deadline in a col and status in another col and would like to give a color (red) to deadlines that are after due date (read week) which dont have the status closed. (if closed whitin do date color green). Can this be done? "Bob Phillips" wrote: No it is not. You can get the week number in an adjacent cell, but there is no weeknumber format. -- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) "Steen" wrote in message ... Hi Is is possible to display a date (2006.11.19) as week number (47)? I have tried to find a usefull format in format cells..., but it seems that is not possible - no "ww" in there... Any help would be appriciated. |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to display date as week number
Hi Bob
Yes why not - in the first place I didn't want to use more columns - do I need more columns to do it this way? "Bob Phillips" wrote: Why don't you use year and week number? -- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) "Bob Phillips" wrote in message ... Yes, you could use conditional formatting. Let's start by assuming the weeknum is in column A, and status is in column B, and the data starts in row 2. Select all of the rows in column A and B that you want to test. Goto FormatConditional Formatting Change Condition 1 to Formula Is Add a formula of =AND($A220,$B2<"Closed") Click the format button Select Pattern tab Choose a colour OK OK This tests for later than week 20 -- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) "Steen" wrote in message ... Hi Bob and all you others that have been kindly to invest time in this issus. I almost new it - it's not possible to do. Could it then be done the other way arround? I am using week number as deadline in a col and status in another col and would like to give a color (red) to deadlines that are after due date (read week) which dont have the status closed. (if closed whitin do date color green). Can this be done? "Bob Phillips" wrote: No it is not. You can get the week number in an adjacent cell, but there is no weeknumber format. -- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) "Steen" wrote in message ... Hi Is is possible to display a date (2006.11.19) as week number (47)? I have tried to find a usefull format in format cells..., but it seems that is not possible - no "ww" in there... Any help would be appriciated. |
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to display date as week number
No, I was suggesting that you add the year number to the weeknumber and just
test as before. 200701 comes after 200652, so it caters for year overlap. -- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) "Steen" wrote in message ... Hi Bob Yes why not - in the first place I didn't want to use more columns - do I need more columns to do it this way? "Bob Phillips" wrote: Why don't you use year and week number? -- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) "Bob Phillips" wrote in message ... Yes, you could use conditional formatting. Let's start by assuming the weeknum is in column A, and status is in column B, and the data starts in row 2. Select all of the rows in column A and B that you want to test. Goto FormatConditional Formatting Change Condition 1 to Formula Is Add a formula of =AND($A220,$B2<"Closed") Click the format button Select Pattern tab Choose a colour OK OK This tests for later than week 20 -- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) "Steen" wrote in message ... Hi Bob and all you others that have been kindly to invest time in this issus. I almost new it - it's not possible to do. Could it then be done the other way arround? I am using week number as deadline in a col and status in another col and would like to give a color (red) to deadlines that are after due date (read week) which dont have the status closed. (if closed whitin do date color green). Can this be done? "Bob Phillips" wrote: No it is not. You can get the week number in an adjacent cell, but there is no weeknumber format. -- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) "Steen" wrote in message ... Hi Is is possible to display a date (2006.11.19) as week number (47)? I have tried to find a usefull format in format cells..., but it seems that is not possible - no "ww" in there... Any help would be appriciated. |
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to display date as week number
Thanks Bob - The solution is sometimes so simple that it's difficult to see :-)
"Bob Phillips" wrote: No, I was suggesting that you add the year number to the weeknumber and just test as before. 200701 comes after 200652, so it caters for year overlap. -- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) "Steen" wrote in message ... Hi Bob Yes why not - in the first place I didn't want to use more columns - do I need more columns to do it this way? "Bob Phillips" wrote: Why don't you use year and week number? -- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) "Bob Phillips" wrote in message ... Yes, you could use conditional formatting. Let's start by assuming the weeknum is in column A, and status is in column B, and the data starts in row 2. Select all of the rows in column A and B that you want to test. Goto FormatConditional Formatting Change Condition 1 to Formula Is Add a formula of =AND($A220,$B2<"Closed") Click the format button Select Pattern tab Choose a colour OK OK This tests for later than week 20 -- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) "Steen" wrote in message ... Hi Bob and all you others that have been kindly to invest time in this issus. I almost new it - it's not possible to do. Could it then be done the other way arround? I am using week number as deadline in a col and status in another col and would like to give a color (red) to deadlines that are after due date (read week) which dont have the status closed. (if closed whitin do date color green). Can this be done? "Bob Phillips" wrote: No it is not. You can get the week number in an adjacent cell, but there is no weeknumber format. -- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) "Steen" wrote in message ... Hi Is is possible to display a date (2006.11.19) as week number (47)? I have tried to find a usefull format in format cells..., but it seems that is not possible - no "ww" in there... Any help would be appriciated. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Mileage Claim Formula | New Users to Excel | |||
count number occurring within specific date range | Excel Worksheet Functions | |||
Wrong Week number | Excel Worksheet Functions | |||
Macro to copy cells to rows below | Excel Discussion (Misc queries) | |||
user defined function | Excel Worksheet Functions |