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.

[TABLE="class: grid, width: 500"]
<tbody>[TABLE="width: 500, align: left"]
<colgroup><col span="3"><col><col></colgroup><tbody>[TR]
[TD="align: right"]Tuesday
[/TD]
[TD="align: right"]01 Aug 13[/TD]
[TD][/TD]
[TD="align: right"]Tuesday[/TD]
[TD="align: right"]01 Aug 13[/TD]
[/TR]
[TR]
[TD="align: right"]Monday
[/TD]
[TD]07 Aug 13
[/TD]
[TD][/TD]
[TD="align: right"]Monday[/TD]
[TD]07 Aug 13[/TD]
[/TR]
[TR]
[TD="align: right"]Monday[/TD]
[TD]14 Aug 13
[/TD]
[TD][/TD]
[TD="align: right"]Monday[/TD]
[TD]14 Aug 13[/TD]
[/TR]
[TR]
[TD="align: right"]Monday[/TD]
[TD]21 Aug 13[/TD]
[TD][/TD]
[TD="align: right"]Monday[/TD]
[TD]21 Aug 13[/TD]
[/TR]
[TR]
[TD="align: right"]Monday[/TD]
[TD]28 Aug 13[/TD]
[TD][/TD]
[TD="align: right"]Monday[/TD]
[TD]28 Aug 13[/TD]
[/TR]
[TR]
[TD="align: right"]Monday[/TD]
[TD]04 Sep 13[/TD]
[TD][/TD]
[TD="align: right"]Monday[/TD]
[TD]04 Sep 13[/TD]
[/TR]
[TR]
[TD="align: right"]Monday[/TD]
[TD]11 Sep 13[/TD]
[TD][/TD]
[TD="align: right"]Monday[/TD]
[TD]11 Sep 13[/TD]
[/TR]
[TR]
[TD="align: right"]Monday[/TD]
[TD]18 Sep 13[/TD]
[TD][/TD]
[TD="align: right"]Monday[/TD]
[TD]18 Sep 13[/TD]
[/TR]
[TR]
[TD="align: right"]Monday[/TD]
[TD]25 Sep 13[/TD]
[TD]➡[/TD]
[TD="align: right"]Monday[/TD]
[TD]25 Sep 13[/TD]
[/TR]
[TR]
[TD="align: right"]Monday[/TD]
[TD]02 Oct 13[/TD]
[TD][/TD]
[TD]Sunday
[/TD]
[TD="align: right"]1 Oct 13
[/TD]
[/TR]
[TR]
[TD="align: right"]Monday[/TD]
[TD]09 Oct 13[/TD]
[TD][/TD]
[TD="align: right"]Monday[/TD]
[TD]02 Oct 13[/TD]
[/TR]
[TR]
[TD="align: right"]Monday[/TD]
[TD]16 Oct 13[/TD]
[TD][/TD]
[TD="align: right"]Monday[/TD]
[TD]09 Oct 13[/TD]
[/TR]
[TR]
[TD="align: right"]Monday[/TD]
[TD]23 Oct 13[/TD]
[TD][/TD]
[TD="align: right"]Monday[/TD]
[TD]16 Oct 13[/TD]
[/TR]
[TR]
[TD="align: right"]Monday[/TD]
[TD]30 Oct 13[/TD]
[TD][/TD]
[TD="align: right"]Monday[/TD]
[TD]23 Oct 13[/TD]
[/TR]
[TR]
[TD="align: right"]Monday[/TD]
[TD]06 Nov 13[/TD]
[TD][/TD]
[TD="align: right"]Monday[/TD]
[TD]30 Oct 13[/TD]
[/TR]
[TR]
[TD="align: right"]Monday[/TD]
[TD]13 Nov 13[/TD]
[TD][/TD]
[TD]Wednesday
[/TD]
[TD="align: right"]1 Nov 2013
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]Monday[/TD]
[TD]06 Nov 13[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]Monday[/TD]
[TD]13 Nov 13[/TD]
[/TR]
</tbody>[/TABLE]
</tbody>[/TABLE]

 

[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))