sheet name into cell

[JOEE1979]

My sheet names are years (2024, 2025, 2026....)
In column A, I would like to put the date starting with 1 Jan and use the sheet name to make the year.
I would like this to continue all the way to 31 Dec (sheet name)

Is this possible?

 

[kevin9999]

Until a more elegant solution comes along:

test.xlsm
	A
1	HDR
2	1 Jan 2024
3	2 Jan 2024
4	3 Jan 2024
5	4 Jan 2024
6	5 Jan 2024
7	6 Jan 2024
8	7 Jan 2024
9	8 Jan 2024
10	9 Jan 2024
11	10 Jan 2024
12	11 Jan 2024
13	12 Jan 2024
14	13 Jan 2024
15	14 Jan 2024
16	15 Jan 2024
17	16 Jan 2024
18	17 Jan 2024
19	18 Jan 2024
20	19 Jan 2024
21	20 Jan 2024
22	21 Jan 2024
23	22 Jan 2024
24	23 Jan 2024
25	24 Jan 2024
26	25 Jan 2024
27	26 Jan 2024
28	27 Jan 2024
29	28 Jan 2024
30	29 Jan 2024
31	30 Jan 2024
32	31 Jan 2024
2024

Cell Formulas
Range	Formula
A2:A32	A2	=ROW()-1&" Jan "&TEXTAFTER(CELL("filename"),"]")

 

[JOEE1979]

Until a more elegant solution comes along:

test.xlsm
	A
1	HDR
2	1 Jan 2024
3	2 Jan 2024
4	3 Jan 2024
5	4 Jan 2024
6	5 Jan 2024
7	6 Jan 2024
8	7 Jan 2024
9	8 Jan 2024
10	9 Jan 2024
11	10 Jan 2024
12	11 Jan 2024
13	12 Jan 2024
14	13 Jan 2024
15	14 Jan 2024
16	15 Jan 2024
17	16 Jan 2024
18	17 Jan 2024
19	18 Jan 2024
20	19 Jan 2024
21	20 Jan 2024
22	21 Jan 2024
23	22 Jan 2024
24	23 Jan 2024
25	24 Jan 2024
26	25 Jan 2024
27	26 Jan 2024
28	27 Jan 2024
29	28 Jan 2024
30	29 Jan 2024
31	30 Jan 2024
32	31 Jan 2024
2024

Cell Formulas
Range	Formula
A2:A32	A2	=ROW()-1&" Jan "&TEXTAFTER(CELL("filename"),"]")

What if I'm starting on row 5 and I have merged rows?

Project tracker (1)1111.xlsx
	A
6	5 Jan 2025
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26	25 Jan 2025
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46	45 Jan 2025
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
2025

Cell Formulas
Range	Formula
A6,A26,A46	A6	=ROW()-1&" Jan "&TEXTAFTER(CELL("filename"),"]")

 

[kevin9999]

If you type the first date in the first merged cell, you could simply drag it down the column.

 

[JOEE1979]

If you type the first date in the first merged cell, you could simply drag it down the column.

I know that, but I'm trying to make an automated worksheet for the following years

 

[kevin9999]

I'll step back, I don't do merged cells. Hopefully you'll get an answer soon.

 

[Cubist]

I adapted @kevin9999 answer, but this doesn't deal with merged cells since you can't spill over them.

=LET(y,NUMBERVALUE(TEXTAFTER(CELL("filename"),"]")),d,IF(OR(MOD(y,400)=0,AND(MOD(y,4)=0,MOD(y,100)<>0)),366, 365),SEQUENCE(d,,0)+DATE(y,1,1))

 

[Cubist]

I should mention that getting the sheet name like this to extract the year is very volatile. I suggest you have a cell that contains the year and reference it instead.

 

[JOEE1979]

what do you mean by volitile?

 

[Cubist]

Here's the first result from a Google search.

You must refresh the calculations every time you're on a certain sheet to get the correct year. Otherwise, the year will show whatever the last time you refresh the calculation.
For example, if I ran the above formula for "2024", if I go to "2025". All the dates will show "2024", I must refresh to get 2025. Now if I go back to 2024, it says 2025.

In short, not a good idea because it can have unexpected consequences.