Problem description & analysis:

In the following Excel table, the value of cell A2 is the date.

    A
1   Fecha
2   01/03/24

We need to generate a list of dates of the month where that date belongs.

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

Solution:

Use SPL XLL to enter the following formula:

=spl("=d=E(?),periods(pdate@m(d),pdate@me(d)).(E(~))",A2)

As shown in the picture below:

Explanation:

E()function converts an Excel date to a numeric value. periods() function generates a list according to the specified beginning time and ending time. pdate@m gets the first day of the month where a specified date belongs; @e function returns the last day of the month.

We can get the whole list even when there are multiple dates in column A:

    A
1   Fecha
2   01/03/24
3   03/03/24
4   05/03/24
5   10/03/24
6   31/03/24
7   02/05/24
8   28/05/24

Get all unique months and generate a list of all days of every month:

    A
1   WholeMonth
2   01/03/24
3   02/03/24
4   03/03/24
5   04/03/24
6   05/03/24
7   06/03/24
8   07/03/24
9   08/03/24
10  09/03/24
11  10/03/24
12  11/03/24
13  12/03/24
14  13/03/24
15  14/03/24
16  15/03/24
17  16/03/24
18  17/03/24
19  18/03/24
20  19/03/24
21  20/03/24
22  21/03/24
23  22/03/24
24  23/03/24
25  24/03/24
26  25/03/24
27  26/03/24
28  27/03/24
29  28/03/24
30  29/03/24
31  30/03/24
32  31/03/24
33  01/05/24
34  02/05/24
35  03/05/24
36  04/05/24
37  05/05/24
38  06/05/24
39  07/05/24
40  08/05/24
41  09/05/24
42  10/05/24
43  11/05/24
44  12/05/24
45  13/05/24
46  14/05/24
47  15/05/24
48  16/05/24
49  17/05/24
50  18/05/24
51  19/05/24
52  20/05/24
53  21/05/24
54  22/05/24
55  23/05/24
56  24/05/24
57  25/05/24
58  26/05/24
59  27/05/24
60  28/05/24
61  29/05/24
62  30/05/24
63  31/05/24

We still use SPL XLL to enter a formula:

=spl("=?.conj().(E(~)).group@u1(month@y(~)).(periods(pdate@m(~),pdate@me(~))).conj().(E(~))",A2:A8)
The conj() function concatenates members of a sequence. month@y gets the month with the corresponding year attached. group@1 gets the first member from each group; @u option means there isn’t a sorting after the grouping operation.

In Excel, Generate a List of All Days of the Month Where a Specified Data Belongs