Week number converted to date

[RJG]

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

 

[Bob Phillips]

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

 

[Guest]

Maybe.........

=38718+1+(7*weeknumber)

Vaya con Dios,
Chuck, CABGx3

 

[Tom Ogilvy]

=A2-weekday(a2,2)+1

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

 

[Carim]

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

 

[RJG]

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

 

[Bob Phillips]

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)