Date Conversion

[Chris Roth]

How can I convert a week number to a month. For example,
week number 5 is the first week in February. How can I
automatically convert that in a worksheet.

 

[Biff]

Hi Chris,

This depends on the calendar year so you need a reference
date as a starting point. I used 2003 as the year.

=TEXT(DATE(2003,1,1)+A1*7,"MMMM") = February

A1 = 5 weeknumber
B1 = 1/1/2003 calendar year starting point

=TEXT(B1+A1*7,"MMMM") = February

Biff

 

[Ron Rosenfeld]

How can I convert a week number to a month. For example,
week number 5 is the first week in February. How can I
automatically convert that in a worksheet.

Well, it depends on your definition of Week Number.

The ISO definition of Weeknumber is that Week 1 starts on the Monday of the
week that includes January 4 (or that includes the first Thursday of the year).

That being the case, the first day of Weeknumber in any given year will be
given by the formula:

=DATE(YYYY,1,4)-WEEKDAY(DATE(YYYY,1,4),3)+7*(Weeknumber-1)

where YYYY is the year.

However, with this definition, the 5th week of certain years will start in
January, not in February.

If you want something else, please post more specifics.


--ron