How To Create A Biweekly Payroll Calendar In Excel

Login
Cart

Training
Videos
Functions
Formulas
Shortcuts
Blog

Next biweekly payday from date

Excel formula: Next biweekly payday from date

Summary

Next biweekly payday from dateTo get the next payday - assuming a biweekly schedule, with paydays on Friday - you can use a formula based on the CEILING function.

In the example shown, the formula in C6 is:

Note: this formula assumes Excel's default 1900 date system.

Explanation

This formula depends on the CELING function, which rounds numbers up to a given multiple. It works because how dates work in Excel's default 1900 date system, where the first day in the system is the number 1, equal to the date Sunday January 1, 1900.

In this scheme, the first Friday is day number 6, the second Friday is day number 13, and day 14 is the second Saturday. What this means is that all second Saturday's in the future are evenly divisible by 14.

The formula uses this fact to figure out 2nd Saturdays, then subtracts 1 to get the Friday previous.

The other every other Friday

If you need to get the alternate Friday in an every other Friday scheme, you can use this version of the formula:

The idea is the same, but the formula needs to roll forward 8 days to get to an even multiple of 14. Once CEILING returns a date, 8 days are subtracted to move back to the Friday previous.

Note: I ran into this formula as an answer on stack overflow by the awesome Barry Houdini.

Related formulas

Excel formula: Next working day

The WORKDAY formula is fully automatic. Given a date and days, it will add days to the date, taking into account weekends and, optionally, holidays. In this case, holidays are supplied as the named range holidays (E4:E8), so holidays are taken into...

Excel formula: Next business day 6 months in future

Working from the inside out, EDATE first calculates a date 6 months in the future. In the example shown, that date is December 24, 2015. Next, the formula subtracts 1 day to get December 23, 2015, and the result goes into the WORKDAY function as the...

Related functions

Excel CEILING function

The Excel CEILING function rounds a given number up to the nearest specified multiple. CEILING works like the MROUND function, but CEILING always rounds up.

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.

Download 100+ Important Excel Functions

Get over 100 Excel Functions you should know in one handy PDF.

Excel video training

Quick, clean, and to the point.

Learn more