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

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
1Fecha
201/03/24

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

A
1WholeMonth
201/03/24
302/03/24
403/03/24
504/03/24
605/03/24
706/03/24
807/03/24
908/03/24
1009/03/24
1110/03/24
1211/03/24
1312/03/24
1413/03/24
1514/03/24
1615/03/24
1716/03/24
1817/03/24
1918/03/24
2019/03/24
2120/03/24
2221/03/24
2322/03/24
2423/03/24
2524/03/24
2625/03/24
2726/03/24
2827/03/24
2928/03/24
3029/03/24
3130/03/24
3231/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:

r/esProc_Desktop - In Excel, Generate a List of All Days of the Month Where a Specified Data Belongs

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
1Fecha
201/03/24
303/03/24
405/03/24
510/03/24
631/03/24
702/05/24
828/05/24

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

A
1WholeMonth
201/03/24
302/03/24
403/03/24
504/03/24
605/03/24
706/03/24
807/03/24
908/03/24
1009/03/24
1110/03/24
1211/03/24
1312/03/24
1413/03/24
1514/03/24
1615/03/24
1716/03/24
1817/03/24
1918/03/24
2019/03/24
2120/03/24
2221/03/24
2322/03/24
2423/03/24
2524/03/24
2625/03/24
2726/03/24
2827/03/24
2928/03/24
3029/03/24
3130/03/24
3231/03/24
3301/05/24
3402/05/24
3503/05/24
3604/05/24
3705/05/24
3806/05/24
3907/05/24
4008/05/24
4109/05/24
4210/05/24
4311/05/24
4412/05/24
4513/05/24
4614/05/24
4715/05/24
4816/05/24
4917/05/24
5018/05/24
5119/05/24
5220/05/24
5321/05/24
5422/05/24
5523/05/24
5624/05/24
5725/05/24
5826/05/24
5927/05/24
6028/05/24
6129/05/24
6230/05/24
6331/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