Enter the first of the month and list days in that month

[Tomwhite]

This is really close to another question about charts but it is a little different:
Because the months change, I want to be able to type the first day of the month in a cell and have the rest of the days automatically fill in, but end at the last day of the month listed. For example, sheet 1 is for January 2021. I want to be able to type 01/01/2021 as a header (I will have it display as Jan-21) then have all of the cells below list all of the days in January, stopping at 01/31/2021. Sheet 2 will be for February 2021. I will enter 02/01/21 and have it fill automatically stopping at 02/28/21. Then 03/01/21 then have it list all days in March without intervention. How do I do that?
Thank you,
Tom

 

[JoeMo]

Drag the formulas down to row 32 to cover the longest months. The cells will remain empty until you enter the first day of the month in A1.

Book1
	A
1	Jan-21
2	1/2/2021
3	1/3/2021
4	1/4/2021
5	1/5/2021
6	1/6/2021
7	1/7/2021
8	1/8/2021
9	1/9/2021
10	1/10/2021
11	1/11/2021
12	1/12/2021
13	1/13/2021
14	1/14/2021
15	1/15/2021
16	1/16/2021
17	1/17/2021
18	1/18/2021
19	1/19/2021
20	1/20/2021
21	1/21/2021
22	1/22/2021
23	1/23/2021
24	1/24/2021
25	1/25/2021
26	1/26/2021
27	1/27/2021
28	1/28/2021
29	1/29/2021
30	1/30/2021
31	1/31/2021
32
Sheet6

Cell Formulas
Range	Formula
A2:A32	A2	=IF(A1="","",IF(A1=EOMONTH(A1,0),"",A1+1))

 

[Fluff]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)
Depending on your version, another option is

+Fluff 1.xlsm
	A
1	01/01/2021
2	02/01/2021
3	03/01/2021
4	04/01/2021
5	05/01/2021
6	06/01/2021
7	07/01/2021
8	08/01/2021
9	09/01/2021
10	10/01/2021
11	11/01/2021
12	12/01/2021
13	13/01/2021
14	14/01/2021
15	15/01/2021
16	16/01/2021
17	17/01/2021
18	18/01/2021
19	19/01/2021
20	20/01/2021
21	21/01/2021
22	22/01/2021
23	23/01/2021
24	24/01/2021
25	25/01/2021
26	26/01/2021
27	27/01/2021
28	28/01/2021
29	29/01/2021
30	30/01/2021
31	31/01/2021
Main

Cell Formulas
Range	Formula
A2:A31	A2	=SEQUENCE(DAY(EOMONTH(A1,0))-1,,A1+1)
Dynamic array formulas.

 

[Fluff]

Forgot to account for a blank cell, so it should be

=IF(A1="","",SEQUENCE(DAY(EOMONTH(A1,0))-1,,A1+1))

 

[Tomwhite]

=IF(A1="","",SEQUENCE(DAY(EOMONTH(A1,0))-1,,A1+1))

Thank you so much for that formula. It got me headed to the right direction. Just a note; This gave me 30 days in January and 27 days in February. I removed the -1 and it seems to work as desired. Thanks again for the help.

 

[Tomwhite]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

I have updated my account details. That should help. I didn't realize that was there.
Tom

 

[Fluff]

Glad we could help & thanks for the feedback.
Thanks also for updating your profile.