Week number converted to date
 

I have used
"=weeknum(A2;2)"
to covert actual dates into the week of the year into which they
fell.ie 009/02/2006 is in week 7.

I now want to be able to have a column heading that reads "Week
comencing 02/022006", so I need to convert week7 back to the first
day(monday) of that week.

Is this possible, if so how.

Bob

Week number converted to date
 

=TODAY()-WEEKDAY(TODAY(),3)-7*WEEKNUM(TODAY()-WEEKDAY(TODAY(),3))+A1*7

where A1 is that week number

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"RJG" wrote in message
ups.com...
I have used
"=weeknum(A2;2)"
to covert actual dates into the week of the year into which they
fell.ie 009/02/2006 is in week 7.

I now want to be able to have a column heading that reads "Week
comencing 02/022006", so I need to convert week7 back to the first
day(monday) of that week.

Is this possible, if so how.

Bob

Week number converted to date
 

Maybe.........

=38718+1+(7*weeknumber)

Vaya con Dios,
Chuck, CABGx3



"RJG" wrote:

I have used
"=weeknum(A2;2)"
to covert actual dates into the week of the year into which they
fell.ie 009/02/2006 is in week 7.

I now want to be able to have a column heading that reads "Week
comencing 02/022006", so I need to convert week7 back to the first
day(monday) of that week.

Is this possible, if so how.

Bob

Week number converted to date
 

=A2-weekday(a2,2)+1

or
="Week commencing " & Text(A2-weekday(a2,2)+1,"dd/mm/yyyy")

--
Regards,
Tom Ogilvy


"RJG" wrote in message
ups.com...
I have used
"=weeknum(A2;2)"
to covert actual dates into the week of the year into which they
fell.ie 009/02/2006 is in week 7.

I now want to be able to have a column heading that reads "Week
comencing 02/022006", so I need to convert week7 back to the first
day(monday) of that week.

Is this possible, if so how.

Bob

Week number converted to date
 

Hi Bob,

If you are looking for the Monday of any given week,
=A2-WEEKDAY(A2)+2
should do the job, provided your date is located in A2.

HTH
Cheers
Carim

Week number converted to date
 

Sorry to be stupid but I am not a regular excel user,

As you correctly state the above gives a number (38733) in the required
cell, if I click on format cell then I can get the number to show
corrctly as 16/01/2006. However as soon as I include text with it the
formatting dissapears and reverts back to the number. Is there anyway I
can format the following straight into the required "Week com
16/01/2006".

="week com
"&TODAY()-WEEKDAY(TODAY();3)-7*WEEKNUM(TODAY()-WEEKDAY(TODAY();3))+L1*7

With thanks

Bob

Week number converted to date
 

Bob,

Try

="week com
"&TEXT(TODAY()-WEEKDAY(TODAY();3)-7*WEEKNUM(TODAY()-WEEKDAY(TODAY();3))+L1*7
,"mm/dd/yyyy")


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"RJG" wrote in message
oups.com...
Sorry to be stupid but I am not a regular excel user,

As you correctly state the above gives a number (38733) in the required
cell, if I click on format cell then I can get the number to show
corrctly as 16/01/2006. However as soon as I include text with it the
formatting dissapears and reverts back to the number. Is there anyway I
can format the following straight into the required "Week com
16/01/2006".

="week com
"&TODAY()-WEEKDAY(TODAY();3)-7*WEEKNUM(TODAY()-WEEKDAY(TODAY();3))+L1*7

With thanks

Bob