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.
Next biweekly payday from date
= CEILING(date +1,14)-1
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 date, because payments are happening on Friday. Therefore, the formula looks like:
Figure 1. Paydays on every other Friday
We can use 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, 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, 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 past. Because of this, we must add number 1 to the date. Since 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 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 date in B3 (26-Oct-18) is the date in the cell C3 (2-Nov-18).