...

In Excel, generate a list of all days of the month where a specified date belongs

 

In the following Excel table, 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

Use SPL XLL to enter the following formula:

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

Picture1png

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.

Source:https://stackoverflow.com/questions/78333909/power-query-calendar-from-month-or-calendar-from-table


 
 •  143 View  •