Semimonthly Pay Schedule in Excel: 15th and Month-End Paydays

A semimonthly pay schedule pays people twice a month on fixed dates, usually the 15th and the last day of the month. In Excel you can build the whole year's schedule from a few date formulas: DATE or EDATE for the 15th, EOMONTH for the month-end, and WORKDAY to move a payday that lands on a weekend or holiday. This guide is for payroll and HR staff, small business owners and anyone checking their own paydays.

Semimonthly vs biweekly pay#

Semimonthly pay is tied to the calendar; biweekly pay is tied to the weekday. That one difference changes the number of paydays and the formulas you need:

If your company pays every other Friday instead, follow the biweekly pay schedule guide. Monthly paydays on a set weekday are covered in how to find the last Friday of the month.

Step 1: List the 15th and month-end paydays#

Put one month per row, with the 15th in one column and the last day in the next. The example uses 2027:

1. In A2, type =DATE(2027,1,15). That is the first mid-month payday.
2. In A3, type =EDATE(A2,1) and fill it down to A13 (Windows: Ctrl+D; Mac: ⌘D). EDATE adds one month and keeps the day, so every row is the 15th.
3. In B2, type =EOMONTH(A2,0) and fill down. EOMONTH returns the last day of the month of A2.

Here is the first half of the year (the weekday is shown to help you check):

EOMONTH handles 28-, 29-, 30- and 31-day months for you, including leap years. The EOMONTH function guide shows the syntax, and first and last day of the month in Excel has more month-boundary formulas.

In Excel 2021 or Microsoft 365, you can make each column in one step. =DATE(2027,SEQUENCE(12),15) spills the twelve 15ths, and =EOMONTH(DATE(2027,SEQUENCE(12),1),0) spills the twelve month-ends.

If your company pays on the 15th and the 30th instead of the last day, February needs care. =MIN(DATE(2027,2,30),EOMONTH(DATE(2027,2,1),0)) returns 28 February: DATE rolls 30 February over to 2 March, and MIN picks the earlier month-end.

Step 2: Move weekend and holiday paydays with WORKDAY#

When a payday falls on a weekend or a holiday, most employers pay on the working day before. WORKDAY does this in one step. List your company holidays in H2:H4, for example 1 January, 31 May and 5 July 2027. Then in C2:

=WORKDAY(A2+1,-1,$H$2:$H$4)

WORKDAY counts working days from a start date, skipping weekends and the holiday list. Starting from the day after the payday and going back one working day gives:

• the payday itself, if it is a working day (Friday 15 January stays Friday 15 January);
• the working day before, if it isn't (Saturday 15 May becomes Friday 14 May).

Use the same formula on the month-end column. Monday 31 May 2027 is on the holiday list, so it moves to Friday 28 May. The $ signs keep the holiday list fixed as you fill down. If your policy is to pay on the next working day instead, use =WORKDAY(A2-1,1,$H$2:$H$4).

The WORKDAY function guide lists the arguments, and NETWORKDAYS and WORKDAY explained covers custom weekends with WORKDAY.INTL.

Step 3: Add the pay period dates#

Each payday covers half a month. The usual split is the 1st to the 15th, paid on the 15th, and the 16th to the last day, paid at month-end. For a 15th payday in A2:

EOMONTH(A2,-1)+1 is the last day of the previous month plus one, which is the 1st of this month. A2+1 is the 16th. Some employers pay a few days after the period ends, to give payroll time to process hours. In that case, keep the period dates and add the delay to the payday, then run it through WORKDAY.

Step 4: Calculate the pay for each period#

For salaried staff, each semimonthly payment is the annual salary divided by 24. A salary of 54,000 gives 2,250 per payday. (On a biweekly schedule the same salary would be 54,000 ÷ 26, about 2,076.92, paid more often.)

For a partial period, such as a new hire, prorate by working days. Say someone starts on Wednesday 6 January 2027. The first period runs from 1 to 15 January, and 1 January is a holiday:

=2250*NETWORKDAYS(DATE(2027,1,6),DATE(2027,1,15),$H$2:$H$4)/NETWORKDAYS(DATE(2027,1,1),DATE(2027,1,15),$H$2:$H$4)

NETWORKDAYS counts 8 working days worked out of 10 in the period, so the first payment is 1,800. Both halves of January 2027 happen to have 10 working days, but that varies from period to period. Hourly staff are simpler: hours worked in the period times the rate. The payroll calculator guide adds overtime, tax and net pay.

Common mistakes in a semimonthly schedule#

These are the slips to check for:

1. Typing 30 or 31 for the month-end. February breaks, and so do 30-day months. Use EOMONTH.
2. Using WORKDAY(payday,-1). That always moves back a day, even when the payday is a normal working day. Start from payday + 1.
3. Forgetting the holiday list, or not locking it with $, so it slides down as you fill.
4. Seeing numbers like 46416 instead of dates. That is the date's serial number. Format the cells as dates (Windows: Ctrl+1; Mac: ⌘1).
5. Mixing up 24 and 26. A semimonthly salary is divided by 24; a biweekly one by 26.

For the month-shifting functions behind all of this, see EDATE and EOMONTH in Excel.

Practise it#

Pay schedules are built from a handful of date functions, and each one is quick to learn by doing. Practise EDATE, EOMONTH and WEEKDAY in a live spreadsheet: you type the formula and it checks it instantly. Then practise NETWORKDAYS and WORKDAY to count working days and move dates around weekends and holidays.