How to Generate Semimonthly Dates in Google Sheets

If you need to generate semimonthly sequential dates in Google Sheets, you can do it efficiently using a combination of functions like SORT, VSTACK, DATE, SEQUENCE, and optionally EOMONTH.

These formulas are highly flexible and allow you to define semimonthly based on your specific needs, such as:

The 1st and 15th of each month
The 1st and 16th of each month
The 15th and the end of each month
The 16th and the end of each month

Let’s explore how to generate dates twice a month in Google Sheets with ease.

Why Generate Semimonthly Dates in Google Sheets?

Semimonthly date sequences are useful when you’re tracking events or tasks that occur twice a month, particularly on fixed days. This is common in scenarios like:

Payroll schedules (e.g., salaries paid on the 1st and 15th)
Billing cycles that occur semi-monthly
Project milestones and progress checks
Reminders for tasks like reporting or invoicing

By automating these sequences with a formula, you reduce manual effort and ensure consistency across your spreadsheets—especially useful in planning tools, dashboards, and templates.

Generic Formulas to Generate Twice-a-Month Dates in Google Sheets

Here are four semimonthly formula patterns you can use:

1. The 1st and 15th of each month

=SORT(
   VSTACK(
      DATE(year, SEQUENCE(n), 1), 
      DATE(year, SEQUENCE(n), 15)
   )
)

2. The 1st and 16th of each month

=SORT(
   VSTACK(
      DATE(year, SEQUENCE(n), 1), 
      DATE(year, SEQUENCE(n), 16)
   )
)

3. The 15th and the end of each month

=SORT(
   VSTACK(
      DATE(year, SEQUENCE(n), 15), 
      EOMONTH(DATE(year, SEQUENCE(n), 15), 0)
   )
)

4. The 16th and the end of each month

=SORT(
   VSTACK(
      DATE(year, SEQUENCE(n), 16), 
      EOMONTH(DATE(year, SEQUENCE(n), 16), 0)
   )
)

You can replace year with your desired year (e.g., 2025), and n with the number of months you want to generate (e.g., 12 for a full year).

Example: Generate Semimonthly Dates in Google Sheets for 2025

To generate semimonthly dates for all of 2025 in a column:

1st and 15th

=SORT(
   VSTACK(
      DATE(2025, SEQUENCE(12), 1), 
      DATE(2025, SEQUENCE(12), 15)
   )
)

Result:
01/01/2025, 15/01/2025, 01/02/2025, …, 15/12/2025

1st and 16th

=SORT(
   VSTACK(
      DATE(2025, SEQUENCE(12), 1), 
      DATE(2025, SEQUENCE(12), 16)
   )
)

Result:
01/01/2025, 16/01/2025, 01/02/2025, …, 16/12/2025

15th and end of each month

=SORT(
   VSTACK(
      DATE(2025, SEQUENCE(12), 15), 
      EOMONTH(DATE(2025, SEQUENCE(12), 15), 0)
   )
)

Result:
15/01/2025, 31/01/2025, 15/02/2025, …, 31/12/2025

16th and end of each month

=SORT(
   VSTACK(
      DATE(2025, SEQUENCE(12), 16), 
      EOMONTH(DATE(2025, SEQUENCE(12), 16), 0)
   )
)

Result:
16/01/2025, 31/01/2025, 16/02/2025, …, 31/12/2025

How These Formulas Work

Let’s break down the logic of this formula:

=SORT(
   VSTACK(
      DATE(2025, SEQUENCE(12), 1), 
      DATE(2025, SEQUENCE(12), 15)
   )
)

SEQUENCE(12) generates numbers from 1 to 12, representing each month of the year.
DATE(2025, SEQUENCE(12), 1) returns the first day of each month.
DATE(2025, SEQUENCE(12), 15) returns the 15th day of each month.
VSTACK stacks both sequences vertically into a single column.
SORT arranges the dates chronologically.

This logic applies to the other formulas, with only the day value (1st, 15th, 16th, or end of month) being adjusted.

Note: If SEQUENCE(n) goes beyond 12, Google Sheets will roll over to the next year automatically. For instance, month 13 becomes January of the following year.

Additional Tips

Descending Order

To generate the same list in descending order:

=SORT(
   VSTACK(
      DATE(2025, SEQUENCE(12), 1), 
      DATE(2025, SEQUENCE(12), 15)
   ), 1, FALSE
)

Horizontal Output

To generate dates in a single row instead of a column, wrap the formula with TRANSPOSE or TOROW:

=TRANSPOSE(
   SORT(
      VSTACK(
         DATE(2025, SEQUENCE(12), 1), 
         DATE(2025, SEQUENCE(12), 15)
      )
   )
)

=TOROW(
   SORT(
      VSTACK(
         DATE(2025, SEQUENCE(12), 1), 
         DATE(2025, SEQUENCE(12), 15)
      )
   )
)

This can be particularly useful for timelines and Gantt chart-style layouts.