List of week's first day but split when one week reach the end of a month

[Shiro26]

Hi

I'm trying to list the dates that represent the begining of each week of the year but, when a week has two months, I would like to split it.
I do not want to use vba to do that, just a single formula.

The bold+underline cell in the table below is the starting date.
In the second line and below, the following formula is typed:
=IF(WEEKDAY(R[-1]C,2)=1,R[-1]C+7,R[-1]C-WEEKDAY(R[-1]C)+9)

Although I've tried few options, I wasn't able to add in the above formula the part that is needed to return the cells in bold only.

Does anyone know how to do that?

Thank you very much in advance for your help.

Shiro.

<tbody>

Tuesday	01 Aug 13		Tuesday	01 Aug 13
Monday	07 Aug 13		Monday	07 Aug 13
Monday	14 Aug 13		Monday	14 Aug 13
Monday	21 Aug 13		Monday	21 Aug 13
Monday	28 Aug 13		Monday	28 Aug 13
Monday	04 Sep 13		Monday	04 Sep 13
Monday	11 Sep 13		Monday	11 Sep 13
Monday	18 Sep 13		Monday	18 Sep 13
Monday	25 Sep 13	➡	Monday	25 Sep 13
Monday	02 Oct 13		Sunday	1 Oct 13
Monday	09 Oct 13		Monday	02 Oct 13
Monday	16 Oct 13		Monday	09 Oct 13
Monday	23 Oct 13		Monday	16 Oct 13
Monday	30 Oct 13		Monday	23 Oct 13
Monday	06 Nov 13		Monday	30 Oct 13
Monday	13 Nov 13		Wednesday	1 Nov 2013
			Monday	06 Nov 13
			Monday	13 Nov 13

<colgroup><col span="3"><col><col></colgroup><tbody>
</tbody>

</tbody>

 

[jorismoerings]

Hi,

table? Bold?
Surely you forgot to add something to this?

 

[Shiro26]

Hi,

table? Bold?
Surely you forgot to add something to this?

Hi

I apologize, I had added a table but it seemed it did not show up correctly...
It looks like i have some troubles putting the table...
thank you for pointing this out.

Turning this01 Aug 13 (Tue)
07 Aug 13 (Mon)
14 Aug 13 (Mon)
21 Aug 13 (Mon)
28 Aug 13 (Mon)
04 Sep 13 (Mon)
11 Sep 13 (Mon)
18 Sep 13 (Mon)
25 Sep 13 (Mon)
02 Oct 13 (Mon)
09 Oct 13 (Mon)
16 Oct 13 (Mon)
23 Oct 13 (Mon)
30 Oct 13 (Mon)
06 Nov 13 (Mon)
13 Nov 13 (Mon)

Into that
01 Aug 13 (Tue)
07 Aug 13 (Mon)
14 Aug 13 (Mon)
21 Aug 13 (Mon)
28 Aug 13 (Mon)
01 Sep 13 (Fri)
04 Sep 13 (Mon)
11 Sep 13 (Mon)
18 Sep 13 (Mon)
25 Sep 13 (Mon)
01 Oct 13 (Sun)
09 Oct 13 (Mon)
16 Oct 13 (Mon)
23 Oct 13 (Mon)
30 Oct 13 (Mon)
01 Nov 13 (Wed)
06 Nov 13 (Mon)
13 Nov 13 (Mon)
20 Nov 13 (Mon)
27 Nov 13 (Mon)
01 Dec 13 (Fri)




Also, I have just managed to do it using the formula (it took me quite some time to figure out out to write it haha):
=IF(WEEKDAY(R[-1]C,2)<>1,R[-1]C-WEEKDAY(R[-1]C)+9,IF(MONTH(R[-1]C)<>MONTH(R[-1]C+7),EOMONTH(R[-1]C,0)+1,R[-1]C+7))

 

[jorismoerings]

Hmm, think you missed something.
take a look at this:

Book1
	A	B	C
1	01 Aug 13 (Tue)		01 Aug 2013 (Thu)
2	07 Aug 13 (Mon)		05 Aug 2013 (Mon)
3	14 Aug 13 (Mon)		12 Aug 2013 (Mon)
4	21 Aug 13 (Mon)		19 Aug 2013 (Mon)
5	28 Aug 13 (Mon)		26 Aug 2013 (Mon)
6	01 Sep 13 (Fri)		01 Sep 2013 (Sun)
7	04 Sep 13 (Mon)		02 Sep 2013 (Mon)
8	11 Sep 13 (Mon)		09 Sep 2013 (Mon)
9	18 Sep 13 (Mon)		16 Sep 2013 (Mon)
10	25 Sep 13 (Mon)		23 Sep 2013 (Mon)
11	01 Oct 13 (Sun)		30 Sep 2013 (Mon)
12	09 Oct 13 (Mon)		01 Oct 2013 (Tue)
13	16 Oct 13 (Mon)		07 Oct 2013 (Mon)
14	23 Oct 13 (Mon)		14 Oct 2013 (Mon)
15	30 Oct 13 (Mon)		21 Oct 2013 (Mon)
16	01 Nov 13 (Wed)		28 Oct 2013 (Mon)
17	06 Nov 13 (Mon)		01 Nov 2013 (Fri)
18	13 Nov 13 (Mon)		04 Nov 2013 (Mon)
19	20 Nov 13 (Mon)		11 Nov 2013 (Mon)
20	27 Nov 13 (Mon)		18 Nov 2013 (Mon)
21	01 Dec 13 (Fri)		25 Nov 2013 (Mon)
22			01 Dec 2013 (Sun)
Sheet1

Cell Formulas
Range	Formula
C2	=IF(MONTH(C1+(7-WEEKDAY(C1,2)+1))>MONTH(C1),EOMONTH(C1,0)+1,C1+(7-WEEKDAY(C1,2)+1))

 

[Shiro26]

Hello Joris,

I apologize, I realized after reading your post that you thought it was year 2013, which is totally understandable.
However, I had put a random number and the year of my example was 2113...

Both formulas below seems to be working but after checking, yours does not handle the change of year.

=IF(AND(WEEKDAY(R[-1]C,2)>1,WEEKDAY(R[-1]C,2)<7),R[-1]C+8-WEEKDAY(R[-1]C,2),IF(WEEKDAY(R[-1]C,2)=7,R[-1]C+1,IF(MONTH(R[-1]C)<>MONTH(R[-1]C+7),EOMONTH(R[-1]C,0)+1,R[-1]C+7)))

=IF(MONTH(R[-1]C+(7-WEEKDAY(R[-1]C,2)+1))>MONTH(R[-1]C),EOMONTH(R[-1]C,0)+1,R[-1]C+(7-WEEKDAY(R[-1]C,2)+1))

To be honest, I had tried to go with the same logic as yous but could not figure out the problem in my attempts, there must be an additional thing to do in your formula to make it pass the barrier of December 31st..

I still thank you for your formula because if the work is necessary within the year it is more understandable.

Thank you for taking the time to help me.

I will mark the post as solved.

Have a great rest of your day.

Shiro

 

[jorismoerings]

Hi Shiro,

Issue of a year change is easily fixed by changing the formula to this:
=IF(MONTH(C1+(7-WEEKDAY(C1,2)+1))<>MONTH(C1),EOMONTH(C1,0)+1,C1+(7-WEEKDAY(C1,2)+1))