Build a Biweekly Pay Schedule in Excel

A biweekly pay schedule in Excel is a list of paydays exactly 14 days apart, usually with the pay period dates next to each one. You only need to type the first payday: Excel adds 14 days for every row after it. This guide builds the full schedule step by step, then uses it to find months with three paydays, years with 27 paydays and paydays that land on a holiday. It's for payroll and HR staff, small-business owners and anyone planning a budget around their pay.

What the finished schedule looks like#

Each row is one payday, with the period of work it pays for. In this example, people are paid every other Friday, and each payday covers the two weeks ending the Saturday before.

Every column comes from the payday in column A, so changing one date updates the whole row. Excel stores dates as whole numbers (one per day), which is why adding 14 moves a date forward two weeks. The date basics lesson explains how that works.

Step 1: List the paydays 14 days apart#

Type the first payday and let a formula add 14 days for each row.

1. In A2, type the first payday: 2/1/2026 or 1/2/2026, depending on your regional date format. Or type =DATE(2026,1,2), which works everywhere.
2. In A3, type =A2+14.
3. Select A3 down to A27 and fill down (Windows: Ctrl+D; Mac: ⌘D).

That gives 26 paydays. Each formula points at the row above, so changing A2 shifts the whole schedule. The Fill Down and Fill Right lesson practises this.

To show the weekday, format the cells with a custom format such as ddd d mmm yyyy (Ctrl+1 on Windows, ⌘1 on Mac). It's a quick check that every payday is a Friday.

You can also skip formulas: type the first date, then use Home > Fill > Series, choose Columns and Date, and set Step value to 14. The guide to AutoFill and Fill Series shows both ways.

Step 2: Make the whole list with one formula#

In Microsoft 365 or Excel 2021, SEQUENCE builds the list in a single cell:

=SEQUENCE(26,1,DATE(2026,1,2),14)

The four arguments are rows (26), columns (1), the start (the first payday) and the step (14 days). The dates spill down 26 rows. Format them as dates, because SEQUENCE returns plain numbers. More patterns, such as weekdays only, are in the guide to creating a sequence of dates.

Step 3: Add the pay period start and end dates#

The pay period is the two weeks of work a payday covers. Subtract days from the payday to get its dates.

In this example the period ends on the Saturday six days before payday, and starts 13 days before that:

Change the 6 to match your own payroll rule. Start to end is always 14 days including both ends, which is why the start is 13 days before the end. To check a period always ends on the right weekday, see how to find the week-ending date.

Step 4: Count paydays per month and find three-payday months#

Most months have two biweekly paydays, but two months a year usually have three. To count the paydays in a month, put the first day of the month in D1 and use:

=COUNTIFS(A2:A27,">="&D1,A2:A27,"<="&EOMONTH(D1,0))

EOMONTH returns the last day of the month, so the formula counts paydays between the 1st and the month-end. With D1 set to 1 January 2026, the result is 3 (2, 16 and 30 January). For this schedule, July 2026 is the other three-payday month.

Build a small table with the first of each month in one column and this formula next to it, and the 3s jump out. The EOMONTH function reference explains the second argument.

Step 5: Check for a 27-payday year#

52 weeks is 364 days, but a year has 365 or 366. So every so often a biweekly schedule fits 27 paydays into one calendar year. Count each year's paydays with COUNTIFS:

=COUNTIFS(A:A,">="&DATE(2027,1,1),A:A,"<="&DATE(2027,12,31))

For the schedule starting on 2 January 2026, 2026 has 26 paydays (the last is 18 December), but 2027 has 27: from 1 January to 31 December 2027. Extend the list far enough to see it.

This matters when you divide an annual salary into paychecks. =52000/26 gives 2,000 per payday, but in a 27-payday year that adds up to more than the salary. How to handle it is a payroll policy decision; the sheet just shows you when it happens. For gross pay, overtime and tax, see the payroll calculator in Excel.

Step 6: Move paydays that land on a holiday#

If a payday falls on a public holiday, many employers pay on the working day before. List the holiday dates in a range, say H2:H12, and use:

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

WORKDAY counts one working day back from the day after the payday. If the payday is a normal working day, you get the payday itself. If it's a holiday or weekend, you get the working day before.

Biweekly vs semi-monthly vs monthly#

Biweekly isn't the same as twice a month, and each schedule needs a different formula.

Don't use EDATE for biweekly pay: it adds whole months, not two weeks. The EDATE function reference and the guide on adding days, months and years to a date show when each function fits.

Practise it#

A pay schedule is just date arithmetic done carefully. Practise EDATE, EOMONTH and WEEKDAY in a live spreadsheet, where each answer is checked instantly. If dates still feel strange, start with how Excel stores dates, then try the exercise on this page.