Dates to week numbers help.

[Mag\(\)\(\)]

I have a list of dates.....10/7/06, 17/7/06, 1/10/06 etc...

These are in column B. I would like to add a week number into column c where
10/7/06 = week10 and 17/7/06=week 11 and 1/10/06 could be week 22.

How can this be done please?

Have been trying for ages to work this out.

TiA

mag()()

 

[Niek Otten]

http://www.cpearson.com/excel/weeknum.htm

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

|I have a list of dates.....10/7/06, 17/7/06, 1/10/06 etc...
|
| These are in column B. I would like to add a week number into column c where
| 10/7/06 = week10 and 17/7/06=week 11 and 1/10/06 could be week 22.
|
| How can this be done please?
|
| Have been trying for ages to work this out.
|
| TiA
|
| mag()()
|
|
|
|

 

[Arvi Laanemets]

Hi

It depends, how a week number is defined for you.

ISO weeknumber (the 1st week of year is one with 1st Thursday in it, all
weeks are 7 days long)
=1+INT((A1-DATE(YEAR(A1+4-WEEKDAY(A1+6)),1,5)+WEEKDAY(DATE(YEAR(A1+4-WEEKDAY
(A1+6)),1,3)))/7)

US weeknumber (Count of weeks starts from 1st Yanuary, a week may be 1-7
days long. Yes, there really exists such weird system .-))) )
=WEEKNUM(A1)
(You must have Analysis Toolpack Add-In activated. When it isn't available,
you have to install Add-In's from Office install CD)


Arvi Laanemets

 

[Ron de Bruin]

You can find all formulas here, also for weeknum
http://www.rondebruin.nl/weeknumber.htm

 

[Mag\(\)\(\)]

Worked a treat ....Thanx.

Now armed with the week number/date can the month be inserted aswell?

Tia


mag()()

 

[Niek Otten]

=month(a1)

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| Worked a treat ....Thanx.
|
| Now armed with the week number/date can the month be inserted aswell?
|
| Tia
|
|
| mag()()
|
|
| | > http://www.cpearson.com/excel/weeknum.htm
| >
| > --
| > Kind regards,
| >
| > Niek Otten
| > Microsoft MVP - Excel
| >
| > | > |I have a list of dates.....10/7/06, 17/7/06, 1/10/06 etc...
| > |
| > | These are in column B. I would like to add a week number into column c
| > where
| > | 10/7/06 = week10 and 17/7/06=week 11 and 1/10/06 could be week 22.
| > |
| > | How can this be done please?
| > |
| > | Have been trying for ages to work this out.
| > |
| > | TiA
| > |
| > | mag()()
| > |
| > |
| > |
| > |
| >
| >
|
|

 

[Mag\(\)\(\)]

Thanx.........worked great. Have been typing this long hand for months.
This will saves us lots of time now.

Thanx again....

mag()()