Semimonthly pay schedule

In this example, the goal is to create a list of pay dates that follow a semimonthly schedule. A semimonthly pay schedule means employees are paid twice a month, usually on fixed dates such as the 1st and 15th or the 15th and the last day of the month. This results in 24 pay periods over the course of a year. For example, in the worksheet shown above, payroll dates are the 1st and 15th of each month. In the worksheet shown above, the formula solves this problem in two steps. Step 1 is to generate a list of all dates between the start date and the end date. Step 2 is to filter those dates so that only those that land on the 1st and 15th remain. The formula in cell D5 looks like this:

=LET(dates,SEQUENCE(B8-B5+1,1,B5),FILTER(dates,(DAY(dates)=1)+(DAY(dates)=15)))

Background study

If you are not familiar with the SEQUENCE function, these links will help you get up to speed quickly:

• How to use the SEQUENCE function - overview
• The SEQUENCE function - 3 min video
• SEQUENCE of dates - 3 min video
• Dynamic Array Formulas - video training

Generating the dates

The first step is to generate all the dates, and this is done with the SEQUENCE function here:

SEQUENCE(B8-B5+1,1,B5) // generate all dates

The SEQUENCE function generates numeric sequences. Excel dates are just large serial numbers, so this part of the formula evaluates like this:

=SEQUENCE(B8-B5+1,1,B5)
=SEQUENCE(45107-44927+1,1,44927)
=SEQUENCE(181,1,44927)

Essentially, we are asking SEQUENCE for 181 sequential dates that start on 44927, which is the serial number for January 1, 2023, in Excel's date system. The SEQUENCE generates an array that contains 181 dates, and these are defined as the variable "date" by the LET function:

=LET(dates,SEQUENCE(B8-B5+1,1,B5)

We use the LET function here to avoid redundancy. We need to use the full range of dates more than once in the formula and creating this array just one time and assigning it to dates means the operation does not need to be repeated. This makes the formula easier to read and understand, and also improves performance.

Filtering the dates

At this point, we have a list of all dates within the target date range and we have named the list dates. The next step is to filter the list to extract just the dates we are about: the paydays that occur on the 1st and 15th of each month. To do this, we use the FILTER function, which is configured like this:

FILTER(dates,(DAY(dates)=1)+(DAY(dates)=15))

The array in FILTER is given as dates, which contains all dates between January 1, 2023, and June 30, 2023. The include argument is set up like this:

(DAY(dates)=1)+(DAY(dates)=15) // 1st and 15th only

Here, we use the DAY function to extract just the day from the date, then use Boolean algebra with addition to construct an OR condition. In other words, we are filtering on dates that land on the 1st or the 15th days of each month. The final result from FILTER is an array of the 12 dates that land on the 1st and 15th of each month in the target date range. For more information on using addition (+) to create "OR logic", see this short video: Array formulas with AND and OR logic.

Alternative formula - 15th and last

The formula above can be easily adapted to return payroll dates that follow different rules. For example, to return a list of payroll dates that fall on the 15th and last day of each month, you can use a formula like this:

=LET(dates,SEQUENCE(B8-B5+1,1,B5),FILTER(dates,(DAY(dates)=15)+(dates=EOMONTH(dates,0))))

The only difference in this formula is that the include logic inside FILTER has been adjusted like this:

(DAY(dates)=15)+(dates=EOMONTH(dates,0)) // 15th and last

The test for the 15th is the same. To test each for an "end of month" date, we use the EOMONTH function. Inside, EOMONTH, we provide 0 for months to return the last day of the month. As before, the two expressions are joined with addition (+) to create OR logic. The screen below shows the result:

WORKDAY adjustment

The formulas above work well, but they don't check what day of the week pay dates land on. In the real world companies often adjust pay dates that land on a holiday or weekend to the previous business day. So, for example, if the 15th of the month lands on a Saturday, the date is adjusted to Friday the 14th. To adjust dates that land on weekends or holidays back to the previous working day, you can use the WORKDAY function with a special configuration like this:

=WORKDAY(date+1,-1)

Essentially, we move forward one day, and then we ask WORKDAY for the previous workday by providing -1 for days. In this configuration, WORKDAY returns the original date if it is a working day. Otherwise, WORKDAY steps back one day at a time and returns the first working day found. You can see the result in the worksheet below:

The original results from the "1st and 15h" formula appear in column D. Column F shows the adjusted dates. Notice that four dates have been shifted back to the previous working day. 

All in one formula

It is possible to combine the formulas above into a single formula, by piping the results from FILTER directly into the WORKDAY function like this:

=LET(dates,SEQUENCE(B8-B5+1,1,B5),WORKDAY(FILTER(dates,(DAY(dates)=1)+(DAY(dates)=15))+1,-1))

This formula will return the same results seen in column F all in one step. In the same way, you can use the formula below to list adjusted dates for the 15th and last day of the month like this:

=LET(dates,SEQUENCE(B8-B5+1,1,B5),WORKDAY(FILTER(dates,(DAY(dates)=15)+(dates=EOMONTH(dates,0)))+1,-1))

In both formulas, we've nested the FILTER formula inside the WORKDAY function as the start_date argument. This causes all the dates returned by FILTER to go through the WORKDAY, and only dates that land on non-working days are adjusted.

Note: to simplify things, I have not provided holidays to WORKDAY, but you can easily add this optional argument to make WORKDAY skip official holidays. See Previous working day for an example.

Legacy Excel

In older versions of Excel, there is no SEQUENCE function or FILTER function, so we can't use the approach explained above. One option is to hardcode the first date into cell D5, then enter this formula in cell D6 and drag copy it down as needed:

=IF(DAY(D5)=1,D5+14,EOMONTH(D5,0)+1)

This formula uses the IF function and the DAY function to check the date in the "cell above" to see if it is the first of the month. If it is, IF returns the date + 14 days. If it is not the first of the month, we use the EOMONTH function to move to the end of the month, then add one day to land on the first day of the next month.

Alternative

To list pay dates that fall on the 15th and last day of the month in older versions of Excel, you can use the same basic process and a formula like this:

=IF(DAY(D5)=15,EOMONTH(D5,0),D5+15)

This formula assumes that the first date in D5 is either the 15th of a month or the last day of a month. As before, this formula uses the IF and DAY functions to check the date in the "cell above". If the day is the 15th, we use the EOMONTH function to move to the end of the month. If the day is not the 15th, we assume it is the last day of the month and add 15 days.