1. ## Date problem (2003)

I'm trying to figure out a formula or function to calculate the first day of a particular whole week in a month. For example, if I wanted to know the first day of the third whole week of June, 2004, I'm looking for a way to produce the result June 20. Since June 1 fell on a Tuesday, the first whole week starts on June 6, the second on June 13 and the third on June 20.

Does anyone know how to do this?

2. ## Re: Date problem (2003)

Try the following
Cell A1 = Year (2004)
Cell A2 = Month (6)
Cell A3 = Number of weeks (3)

=DATE(A1,A2,1)+1-WEEKDAY(DATE(A1,A2,1))+(IF(WEEKDAY(DATE(A1,A2,1))= 1,A3-1,A3))*7

How it works:

DATE(A1,A2,1)+1-WEEKDAY(DATE(A1,A2,1))
This takes the first of the month then subtracts the number of days to get the date for Sunday of that week.

(IF(WEEKDAY(DATE(A1,A2,1))=1,A3-1,A3))*7
This works out how many days to add to the first part of the equation.

3. ## Re: Date problem (2003)

I assume the week starts with Sunday. This should work, test it a bit more than I have:
<table border=1><td></td><td align=center>A</td><td align=center>B</td><td align=center>C</td><td align=center>1</td><td align=right>6/1/2004</td><td align=right>=A1+IF(WEEKDAY(A1,2),7-WEEKDAY(A1,2),)</td><td>1st Sun</td><tr><td align=center>2</td><td align=right>

4. ## Re: Date problem (2003)

<img src=/S/hello.gif border=0 alt=hello width=25 height=29> Andrew

Apart from Tony and John, I elected to use a User Defined Function implemented via VBA.

I think that it will give you a wider range of capabilities, such as truely return the full week's starting date. In the case of February and October 2004, the last day happens to be a Sunday, but I am not sure if you want to count this as a full week.

The code is almost self documented, so if you have any questions, please let me know.

Wassim

5. ## Re: Date problem (2003)

Thanks, Tony. That did just what I want. I must be getting slow in my old age not to figure that out!

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•