displaying "week of July 29, 2007"

[bbcrock]

I have an excel function that displays the date of an event as a the
numbered week of the year (1,2,29, etc). The clients want to display
all dates between July 29, 2007 and August 3, 2007 using the phrase
"Week of July 29, 2007." Of course I need this function to work for
the whole year. I have the week number, how can I convert that into
the date of the first monday in the week.

The function looks like:
=TRUNC(((A7-DATE(YEAR(A7),1,1))/7))+1+IF(WEEKDAY(DATE(YEAR(A7),
1,1))>WEEKDAY(A7),1,0)

thanks!

 

[Guest]

Monday can be found simply with this
=(A1-(WEEKDAY(A1)-2))
date is in A1 weekday tells the number of the day 1-7, subtract 2 because
monday is the second day.

 

[Ron Rosenfeld]

I have an excel function that displays the date of an event as a the
numbered week of the year (1,2,29, etc). The clients want to display
all dates between July 29, 2007 and August 3, 2007 using the phrase
"Week of July 29, 2007." Of course I need this function to work for
the whole year. I have the week number, how can I convert that into
the date of the first monday in the week.

The function looks like:
=TRUNC(((A7-DATE(YEAR(A7),1,1))/7))+1+IF(WEEKDAY(DATE(YEAR(A7),
1,1))>WEEKDAY(A7),1,0)

thanks!

You already have the date in A7. Assuming your "week of" is to start on the
preceding Sunday, you can use this formula:

=TEXT(A7-WEEKDAY(A7)+1,"""Week of ""mmmm d, yyyy.")


--ron