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.