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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.