How to display date as week number

S

Steen

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.
 
B

Bob Phillips

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)
 
P

paul

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.
 
O

OC

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...
 
S

SteveW

There was a link to this a few days back.

Week number is defined in some special ISO standard

Steve
 
S

Steen

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?
 
S

Steen

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
 
B

Bob Phillips

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 Format>Conditional Formatting
Change Condition 1 to Formula Is
Add a formula of =AND($A2>20,$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)
 
S

Steen

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...
 
B

Bob Phillips

Why don't you use year and week number?

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)
 
S

Steen

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?
 
B

Bob Phillips

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)
 
Top