First Monday in the Year

[Thifiell]

Hello

I have seen a topic like that already here, but my question is a bit different. I don't have any column with all dates, I have a sheet where I must write a logistic plan which goes by days of the week.
For instance 2 transports on Monday, 4 on Tuesday, 2 on Wednesday and 5 on Friday.

and this is for week 6 (February). I need to do it for all others weeks, this year and for next 10 years for example. I would like to write it so I can just copy paste formulas, so I would like to have for January first Monday, first Monday, after first Tuesday 4x and so on. I am sure that all dates in Excel are somehow recorded and we can recall them. Do you have any idea how to write it so I can just copy/paste for next months and years?

 

[jasonb75]

You don't need a column with all dates, but you do need a reference point to start from. If you copy and paste formulas to a new sheet, they will not remember the old dates and increase automatically. They are always relative to the current data.

Try this on a blank sheet.
In A1, enter the year, 2019
In A2, enter the week number, 6
In A3 and fill down, =IF((MOD(ROWS(A$2:A3)-1,13)+1)=1,A2+1,A2)
In B2 =DATE(A1,1,8)-WEEKDAY(DATE(A1,1,6))-7+((A2-1)*7)
In B3 and fill down =$B$2+LOOKUP(MOD(ROWS(B$2:B3)-1,13)+1,{1,3,7,9},{0,1,2,4})+(A3-$A$2)*7

Filling down as far as row 14 returns exactly the same as your example, filling down to row 27 repeats the pattern for week 7, down to row 40 repeats it for week 8.

Does this give you something you can work with?

 

[Thifiell]

You don't need a column with all dates, but you do need a reference point to start from. If you copy and paste formulas to a new sheet, they will not remember the old dates and increase automatically. They are always relative to the current data.

Try this on a blank sheet.
In A1, enter the year, 2019
In A2, enter the week number, 6
In A3 and fill down, =IF((MOD(ROWS(A$2:A3)-1,13)+1)=1,A2+1,A2)
In B2 =DATE(A1,1,8)-WEEKDAY(DATE(A1,1,6))-7+((A2-1)*7)
In B3 and fill down =$B$2+LOOKUP(MOD(ROWS(B$2:B3)-1,13)+1,{1,3,7,9},{0,1,2,4})+(A3-$A$2)*7

Filling down as far as row 14 returns exactly the same as your example, filling down to row 27 repeats the pattern for week 7, down to row 40 repeats it for week 8.

Does this give you something you can work with?

Already blocked on the first step with a first function

 

[Thifiell]

For B3 formula also error, did it work for you?

 

[Thifiell]

Sorry for late reply, it took some time to reinstall Excel from French into English version for our IT dpt :D

 

[henrik2h]

Mayby you have semi-colon separated formulas
Try =IF((MOD(ROWS(A$2:A3)-1;13)+1)=1;A2+1;A2)

 

[jasonb75]

Noting your comment about changing French to English, I suspect that as @henrik2h said, you need to use semi-colons instead of commas. This will apply to all of the formulas, not just the one that henrik has changed for you as an example.

The formulas worked fine for me, I copied them to the forum straight from the sheet that I used to test them. See if the suggested change works for you, if not we will have to dig a little deeper to find the problem.

 

[Thifiell]

Yeah, that has worked with first column, thanks! I did not know you use commas in US in Excel instead.
I still don't understand what should show B column? What is 43500? The logic is well though, I see 2x 43500 for Monday and 4 of 43501 for Tuesday, like it should be

Probably the first screenshot in a first message is not so clear, here is a full worksheet for January 2020
This is what I did manually for January 2020. The problem is that I would like Excel prepares automatically this table by day of weeks
P.S. Just to make it clearer, lundi is Monday in French, Tue is mardi, after mercredi, jeudi and vendredi is Friday.

 

[Thifiell]

It is easier to do by displaying a date in a proper format in B and C column and simply changing the date in B row into a week number.

 

[Thifiell]

...changing the date in B row into a week number in column A.