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

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))))`.

**Excel:** `=SORT(VSTACK(EDATE(B1,SEQUENCE(B2,1,0))+14,EOMONTH(B1,SEQUENCE(B2,1,0))))` (Excel 2024 and Microsoft 365)

**Google Sheets:** `=ARRAYFORMULA(SORT(VSTACK(EDATE(B1,SEQUENCE(B2,1,0))+14,EOMONTH(B1,SEQUENCE(B2,1,0)))))`

## Example

Sample data (row 1 is the header):

| A | B | C | D |
| --- | --- | --- | --- |
| First month | 2026-01-01 |  | Paydays |
| Months | 3 |  |  |

Formula in D2: `=SORT(VSTACK(EDATE(B1,SEQUENCE(B2,1,0))+14,EOMONTH(B1,SEQUENCE(B2,1,0))))`

Result (spills 6 × 1): 1/15/2026; 1/31/2026; 2/15/2026; 2/28/2026; 3/15/2026; 3/31/2026

## 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.

## Other ways

- `=LET(m,EOMONTH(B1,SEQUENCE(B2,1,-1))+1,SORT(VSTACK(m+14,EOMONTH(m,0))))`: Starts 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.

## 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.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/list-semi-monthly-pay-dates