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

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

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Mag()()" <(E-Mail Removed)> wrote in message news:(E-Mail Removed)...
|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()()
|
|
|
|

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



"Mag()()" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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()()
>
>
>
>

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


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Arvi Laanemets" <(E-Mail Removed)> wrote in message news:eKvluq$(E-Mail Removed)...
> 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
>
>
>
> "Mag()()" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> 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()()
>>
>>
>>
>>
>
>

Worked a treat ....Thanx.

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

Tia


mag()()


"Niek Otten" <(E-Mail Removed)> wrote in message
news:u2YYbo$(E-Mail Removed)...
> http://www.cpearson.com/excel/weeknum.htm
>
> --
> Kind regards,
>
> Niek Otten
> Microsoft MVP - Excel
>
> "Mag()()" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> |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()()
> |
> |
> |
> |
>
>

=month(a1)

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Mag()()" <(E-Mail Removed)> wrote in message news(E-Mail Removed)...
| Worked a treat ....Thanx.
|
| Now armed with the week number/date can the month be inserted aswell?
|
| Tia
|
|
| mag()()
|
|
| "Niek Otten" <(E-Mail Removed)> wrote in message
| news:u2YYbo$(E-Mail Removed)...
| > http://www.cpearson.com/excel/weeknum.htm
| >
| > --
| > Kind regards,
| >
| > Niek Otten
| > Microsoft MVP - Excel
| >
| > "Mag()()" <(E-Mail Removed)> wrote in message
| > news:(E-Mail Removed)...
| > |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()()
| > |
| > |
| > |
| > |
| >
| >
|
|

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

Thanx again....

mag()()


"Niek Otten" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
> =month(a1)
>
> --
> Kind regards,
>
> Niek Otten
> Microsoft MVP - Excel
>
> "Mag()()" <(E-Mail Removed)> wrote in message
> news(E-Mail Removed)...
> | Worked a treat ....Thanx.
> |
> | Now armed with the week number/date can the month be inserted aswell?
> |
> | Tia
> |
> |
> | mag()()
> |
> |
> | "Niek Otten" <(E-Mail Removed)> wrote in message
> | news:u2YYbo$(E-Mail Removed)...
> | > http://www.cpearson.com/excel/weeknum.htm
> | >
> | > --
> | > Kind regards,
> | >
> | > Niek Otten
> | > Microsoft MVP - Excel
> | >
> | > "Mag()()" <(E-Mail Removed)> wrote in message
> | > news:(E-Mail Removed)...
> | > |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()()
> | > |
> | > |
> | > |
> | > |
> | >
> | >
> |
> |
>
>