In this tutorial, you will learn how to get a next biweekly payday from date. To be able to calculate this date, you will have to get familiar with the CEILING function. The CEILING function rounds the date up to the nearest date divisible by the defined number, called significance. Also, you will learn how to find the alternate day in the biweekly payment schedule.
Find the next biweekly payday from a date in Excel
Formula using CEILING
= CEILING(date +1,14)-1
Explanation of formula
This function rounds the number up to the nearest number divisible by the defined number, called significance. Parameters of the function are Number – a date from which we want to calculate the next biweekly payday. Significance – is the multiple to which we want to round. As you will see in the example below, Saturday is always divisible by 7 while the alternate Saturday is divisible by 14.
We want to find next biweekly payday from a date because payments are happening on Friday. Therefore, the formula looks like:
Figure 1. Paydays on every other Friday
We can use the CEILING function to find the Saturday since dates in excel are numbers. All dates on Saturday are divisible by 7 and for the alternate Saturday with 14. Because of that, we use the number 14 as significance in the formula. As a result, the CEILING formula will return the Saturday from the defined date in cell B3.
Now we will explain why we add number one to the cell B3. If the date is on Saturday, the CEILING formula will return us the same date. Then we will subtract that date with number 1, and get the date on Friday that is in the past. Because of this, we must add number 1 to the date. Since the CEILING formula result is always the alternate Saturday, we must subtract that date with number 1 to get the date on Friday. Finally, the next biweekly payday from a date in B3 (26-Oct-18) is the date in the cell C3 (26-Oct-18).
In the second example, we want to find the alternate Friday from the previous example payment date results.
Here is the formula used for this example:
Figure 2. The Alternate Friday in an every other Friday
Formula logic is almost the same as in the previous example. We only add number 8 in CEILING function and subtract the formula result with the same number. In that way, you will find the alternate Friday in every other Friday. As a result, the alternate Friday from the next biweekly payday from a date in B3 (26-Oct-18) is the date in the cell C3 (2-Nov-18).
Still need some help with Excel formatting or have other questions about Excel? Connect with a live Excel expert here for some 1 on 1 help. Your first session is always free.