Automate First Monday date for each week of that year

[Alex6175]

Hello,
I was wondering if anybody could help me please?
I am currently working with a timetable spreadsheet that requires me to enter the First Monday date for each week of that year manually i was wondering if anyone could point me towards automating this so i just have to enter the first week of the term for that year and then it will automatically populates the other Mondays until the end of that year.
Thank you in advance for any help.
Best Regards,
Alex
Capture.JPG

[MarvinP]

Hi Alex,

If you find the first Monday of any year, then all others will be 7, 14, 21, ... multiples of 7 added onto that first Monday.

Does that help?

[Alex6175]

Thanks MarvinP,
I get that but how would that be created into a function i don't get so for example 26 February 2018 + 7 would give 33rd February... i need the cell to know that would be 5th March and so on...

[FlameRetired]

Thanks MarvinP,
I get that but how would that be created into a function i don't get so for example 26 February 2018 + 7 would give 33rd February... i need the cell to know that would be 5th March and so on...

If you aren't aware of it dates are numbers. Formatting is cosmetic. What Excel "sees" in 26 February 2018 is the five digit number 43157. Adding 7 returns 43164. Formatting for date presents 3/5/2018.

Does this help?

Edit BW types faster than I do.

[FlameRetired]

There are simpler ways to do this I am sure. They escape me at the moment.

With the start date in A1 array enter this in A2 to get the first Monday.

If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.

=LOOKUP(1E+306,IF(WEEKDAY(ROW(INDIRECT(A1&":"&A1+6)),1)=2,ROW(INDIRECT(A1&":"&A1+6))))

Then in A3 enter this non array formula and fill down as far as desired.

=A2+7

	A
1	1/1/2018
2	1/1/2018
3	1/8/2018
4	1/15/2018
5	1/22/2018
6	1/29/2018
7	2/5/2018
8	2/12/2018
9	2/19/2018
10	2/26/2018
11	3/5/2018
12	3/12/2018
13	3/19/2018
14	3/26/2018
15	4/2/2018
16	4/9/2018
17	4/16/2018
18	4/23/2018
19	4/30/2018
20	5/7/2018
21	5/14/2018
22	5/21/2018
23	5/28/2018
24	6/4/2018
25	6/11/2018

[BoredWorker]

i think i should not a problem excel with auto change to 5th March if it time format.
Excel assume 1 as 1/1/1900 and +1 day will equal to 2/1/1900

you may try

in B1 : 1-1-18
in C1: =B1+7

then format it in
more number formats... > custum > d.m.yy

or

use TEXT function to do so

B1:=TEXT(43101,"d.m.yy")
C1 and remain =TEXT(43101+7*COLUMN(A1),"d.m.yy")

and delete when you see the year of 2019

[Alex6175]

Thank you all for your help got it done... i forgot it records it in just numbers thanks MarvinP and FlameRetired.

[FlameRetired]

You're welcome. Thanks for the feedback.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.