Spreadsheet Practice

How to list semi-monthly pay dates (the 15th and last day) in Excel

Excel formula

=SORT(VSTACK(EDATE(B1,SEQUENCE(B2,1,0))+14,EOMONTH(B1,SEQUENCE(B2,1,0))))

Stack the 15ths and the month ends and sort them: =SORT(VSTACK(EDATE(B1,SEQUENCE(B2,1,0))+14,EOMONTH(B1,SEQUENCE(B2,1,0)))).

Result on the example
a 6-row list that spills from D2 in D2
Works in
Excel 2024 and Microsoft 365
Google Sheets
Different formula, see below

See it work

The formula is already in D2. Change any input and the result updates, or edit the formula to experiment.

How it works

EDATE(B1,SEQUENCE(B2,1,0)) lists the 1st of each month, and +14 turns those into the 15th. EOMONTH(B1,SEQUENCE(B2,1,0)) lists the month ends. VSTACK stacks the two lists and SORT puts them in date order.

=SORT(VSTACK(EDATE(B1,SEQUENCE(B2,1,0))+14,EOMONTH(B1,SEQUENCE(B2,1,0))))

Result{6 rows × 1 col} 46037, 46053, 46068, 46081, …

  1. B11/1/2026

    The value in B1.

  2. B23

    The value in B2.

  3. SEQUENCE(B2,1,0){3 rows × 1 col} 0, 1, 2

    SEQUENCE: A grid of sequential numbers. Spills.

    • rowsB23
    • columns1
    • start0
  4. EDATE(B1,SEQUENCE(B2,1,0)){3 rows × 1 col} 46023, 46054, 46082

    EDATE: Date a number of months before or after.

    • start_dateB11/1/2026
    • monthsSEQUENCE(B2,1,0){3 rows × 1 col} 0, 1, 2
  5. EDATE(B1,SEQUENCE(B2,1,0))+14{3 rows × 1 col} 46037, 46068, 46096

    The + operator adds.

  6. SEQUENCE(B2,1,0){3 rows × 1 col} 0, 1, 2

    SEQUENCE: A grid of sequential numbers. Spills.

    • rowsB23
    • columns1
    • start0
  7. EOMONTH(B1,SEQUENCE(B2,1,0)){3 rows × 1 col} 46053, 46081, 46112

    EOMONTH: Last day of the month, months away.

    • start_dateB11/1/2026
    • monthsSEQUENCE(B2,1,0){3 rows × 1 col} 0, 1, 2
  8. VSTACK(EDATE(B1,SEQUENCE(B2,1,0))+14,EOMONTH(B1,SEQUENCE(B2,1,0))){6 rows × 1 col} 46037, 46068, 46096, 46053, …

    VSTACK: Stacks ranges on top of each other.

    • array1EDATE(B1,SEQUENCE(B2,1,0))+14{3 rows × 1 col} 46037, 46068, 46096
    • array2EOMONTH(B1,SEQUENCE(B2,1,0)){3 rows × 1 col} 46053, 46081, 46112
  9. SORT(VSTACK(EDATE(B1,SEQUENCE(B2,1,0))+14,EOMONTH(B1,SEQUENCE(B2,1,0)))){6 rows × 1 col} 46037, 46053, 46068, 46081, …

    SORT: Sorts rows by one or more columns. is_ascending is TRUE or FALSE.

    • rangeVSTACK(EDATE(B1,SEQUENCE(B2,1,0))+14,EOMONTH(B1,SEQUENCE(B2,1,0))){6 rows × 1 col} 46037, 46068, 46096, 46053, …

In Google Sheets

Google Sheets needs a different formula: =ARRAYFORMULA(SORT(VSTACK(EDATE(B1,SEQUENCE(B2,1,0))+14,EOMONTH(B1,SEQUENCE(B2,1,0))))). On the same data it gives the same result. Here it is running in Google Sheets mode.

Other ways to do it

Each one was run on the same example sheet.

FormulaResultWhen to use it
=LET(m,EOMONTH(B1,SEQUENCE(B2,1,-1))+1,SORT(VSTACK(m+14,EOMONTH(m,0))))spills 6 × 1Starts from the 1st of each month itself, so B1 can be any day in the first month.

Common mistakes

  • Paydays on weekends usually move to the business day before: wrap the list in WORKDAY(…+1,-1,holidays).
  • B1 must be the 1st of the month for +14 to land on the 15th, or use the LET version.

Try it yourself

Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.

List semi-monthly pay dates (the 15th and last day)

In D2, list the paydays on the 15th and the last day of each month, for the number of months in B2 starting with the month in B1.

Questions

How do I list biweekly dates instead?

=SEQUENCE(26,1,first_payday,14).

How many semi-monthly paydays are in a year?

Always 24: two per month.

Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.