Search code examples
excelexcel-formulacalendar

Generate all days of a gived monts in row

i need to generate in automatic and in single row all days of a gived months. For example, if month is October i need:

ROW DATE DAY NOTE
1 01/10/2022 Saturday ...
2 02/10/2022 Sunday ...
3 03/10/2022 Monday ...
... ... ... ...
30 30/10/2022 Sunday ...
31 31/10/2022 Monday ...

How i can to do it? Some macro? Thanks very much.

EDIT: Screenshot

Solution

  • Sharing as an alternative to VBA. Custom formula created using LAMBDA()

    FORMULA_SOLUTION

    • Formula used in cell B4

    =DATE.SERIES(C2)

    The Excel LAMBDA() function gives us a way create custom functions that can be reused throughout a workbook, without using VBA, with a friendly name.

    The formula used in Name Manager as shown below with testing syntax

    =LAMBDA(text,
LET(_month,MONTH(text&0),
_firstDay,DATE(2022,_month,1),
_lastDay,EOMONTH(_firstDay,0),
_dates,SEQUENCE(DAY(_lastDay),,_firstDay),
_day,TEXT(_dates,"dddd"),
VSTACK({"📅 DATE","🗓️ DAY"},HSTACK(_dates,_day))))(C2)

    Copy the above formula, not including the testing parameters at the end, press CTRL+F3, this opens the Name Manager, click New.

    In the New Name dialog, enter the name "DATE.SERIES", leave the scope set to workbook, and paste the formula you copied into the "Refers to" input area, press OK.

    Now that the LAMBDA() formula has a name, it can be used in the workbook like any other function. Note since dates are stored as number in Excel, it will show as numbers, therefore you need to format as per your desire date format.

    Here is a quick gif:

    enter image description here