Need to generate all the dates for a selected month in Google Sheets? You can do that with a single array formula.

Whether you’re creating a monthly attendance sheet, planner, timesheet, or calendar, the formula below automatically returns every date in the selected month based on the year in A1 and the month selected from the drop-down in A2.

It works for every month, including leap years, and you can return the dates either horizontally or vertically.

Quick Answer

Use the following LET and SEQUENCE formula to generate every date for the selected month. The formula automatically updates whenever you change the month or year.

=LET(y, A1, m, A2, dt, DATE(y, MONTH(m&1), 1), IF(LEN(y), SEQUENCE(1, EOMONTH(dt, 0)-dt+1, dt),))

How to Generate Dates for an Entire Month Using a Drop-Down in Google Sheets

First, create the month drop-down in cell A2.

Steps

1. In cell A2, go to Insert > Drop-down and create a drop-down list containing the month names.
2. If you need help creating the drop-down, see my tutorial The Best Data Validation Examples in Google Sheets.
3. The month drop-down is now ready.

You can also create a year drop-down in cell A1, but I prefer entering the year manually.

Use the following formula in B2 and B3.

=LET(y, A1, m, A2, dt, DATE(y, MONTH(m&1), 1), IF(LEN(y), SEQUENCE(1, EOMONTH(dt, 0)-dt+1, dt),))

The formula returns valid date values.

Tip: To return the dates in a column instead of a row, simply wrap the formula with TOCOL.

Formatting the Dates as Weekdays or Day Numbers

To display the weekday names:

1. Select the range B2:AF2.
2. Go to Format > Number > Custom number format.
3. Enter ddd and click Apply.

To display the day numbers:

1. Select the range B3:AF3.
2. Go to Format > Number > Custom number format.
3. Enter dd and click Apply.

You can now generate every date in the selected month and format the results however you like.

Anatomy of the Formula

The following part of the formula returns the first date of the selected month and year.

Formula:

DATE(y, MONTH(m&1), 1)

Syntax:

DATE(year, month, day)

The year is entered in A1, and the month name is selected in A2. Since DATE requires a month number, MONTH(A2&1) converts the month name into its corresponding month number.

For example, if the year is 2023 and the month is February, the formula returns 01/02/2023 (DD/MM/YYYY).

LET assigns the name dt to this date. Instead of repeating the DATE formula later, we simply reference dt, making the formula easier to read and slightly more efficient.

LET(dt, DATE(y, MONTH(m&1), 1),

Next, SEQUENCE generates every date in the selected month using the value stored in dt.

Formula:

SEQUENCE(1, EOMONTH(dt, 0)-dt+1, dt)

Syntax:

SEQUENCE(rows, [columns], [start], [step])

Where:

• rows: 1 (returns the dates in a single row).
• columns: EOMONTH(dt, 0)-dt+1 (calculates the total number of days in the selected month).
• start: dt (the first date of the selected month).

The remaining arguments in SEQUENCE are optional.

Finally, the IF(LEN(y), …) wrapper prevents the formula from returning dates when the year cell is blank.

FAQ

Does the formula work for leap years?

Yes. Since it uses EOMONTH, February automatically returns 28 or 29 days depending on the selected year.

Can I return the dates in a column?

Yes. Simply wrap the formula with TOCOL.

Can I use a year drop-down instead of typing the year?

Yes. Replace the manually entered year with a year selected from another drop-down.

Conclusion

This formula provides a simple way to generate every date for a selected month in Google Sheets. By combining LET, DATE, EOMONTH, and SEQUENCE, it automatically adjusts to the selected month and year, including leap years, without requiring any manual updates.