Date problem (2003)

Topic

New Reply

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?

Reply | Quote

Replies

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.

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

I assume the week starts with Sunday. This should work, test it a bit more than I have:

	A	B	C
1	6/1/2004	=A1+IF(WEEKDAY(A1,2),7-WEEKDAY(A1,2),)	1st Sun
2

Reply | Quote

I assume the week starts with Sunday. This should work, test it a bit more than I have:

	A	B	C
1	6/1/2004	=A1+IF(WEEKDAY(A1,2),7-WEEKDAY(A1,2),)	1st Sun
2

Reply | Quote

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

Reply | Quote

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

Reply | Quote