In Excel, Generate a List of All Days of the Month Where a Specified Data Belongs
Problem description & analysis:
In the following Excel table, the value of cell A2 is 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; @uoption means there isn’t a sorting after the grouping operation.
SPL XLL is now FREE to download and ready to amaze you with its super effectiveness! Check this link: http://www.scudata.com/download-Desktop