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!

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.

--
-John
Please rate when your question is answered to help us and others know what
is helpful.


"(E-Mail Removed)" wrote:

> 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!
>
>

On Thu, 26 Jul 2007 14:29:22 -0000, (E-Mail Removed) wrote:

>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