Go Back

Next biweekly payday from date

Read time: 20 minutes

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. 

Example 1

We want to find next biweekly payday from a date because payments are happening on Friday. Therefore, the formula looks like:

= CEILING(B3+1,14)-1

 

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).

Example 2

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:

= CEILING(B3+8,14)-8

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. 

Did this post not answer your question? Get a solution from connecting with the expert.

Another blog reader asked this question today on Excelchat:
Here are some problems that our users have asked and received explanations on

excel help calculate biweekly totals
Solved by C. A. in 30 mins
I can't get this formula correct, can someone assist? I am trying to create a date for our posting process based on the payment terms. If a customer is paying biweekly, with a start date of 3/1/2018, etc. I want to be able to run daily what customers will need to be charged. =IF(OR(D2="MONTHLY",$C$6+30,")*IF(D2="WEEKLY",$C$6+7,"")*IF(D2="BIWEEKLY",$C$6+14,""""))
Solved by S. H. in 13 mins
I am hoping to take a spreadsheet that has variable number of columns per client and have excel recognize the same client and total their earnings; 00021427 Abbigayle R Hogan (00021427) 08/12/2017 - 08/25/2017 (Biweekly (Student Schedule)) President's Student Aid Pay 0.00 00021427 Abbigayle R Hogan (00021427) 08/12/2017 - 08/25/2017 (Biweekly (Student Schedule)) President's Student Aid Pay 54.00 00042777 Abigail E Vinson (00042777) 08/12/2017 - 08/25/2017 (Biweekly (Student Schedule)) President's Student Aid Pay 0.00 00042777 Abigail E Vinson (00042777) 08/12/2017 - 08/25/2017 (Biweekly (Student Schedule)) President's Student Aid Pay 22.50 00042813 Ainsley Jane Rothschild (00042813) 08/12/2017 - 08/25/2017 (Biweekly (Student Schedule)) President's Student Aid Pay 0.00 00042813 Ainsley Jane Rothschild (00042813) 08/12/2017 - 08/25/2017 (Biweekly (Student Schedule)) President's Student Aid Pay 59.82 00042813 Ainsley Jane Rothschild (00042813) 08/26/2017 - 09/08/2017 (Biweekly (Student Schedule)) President's Student Aid Pay (59.82) 00050041 Alaina M Davis (00050041) 08/26/2017 - 09/08/2017 (Biweekly (Student Schedule)) President's Student Aid Pay 16.32 00050041 Alaina M Davis (00050041) 08/26/2017 - 09/08/2017 (Biweekly (Student Schedule)) President's Student Aid Pay 0.00
Solved by G. U. in 23 mins

Leave a Comment

avatar