Automate First Monday date for each week of that year

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?

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.

Formula:

Please Login or Register  to view this content.

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

Formula:

Please Login or Register  to view this content.

	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

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

Originally Posted by 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...

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.

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.