Skip to content
xlsoffice. All Rights Reserved
  • Home
  • Excel For Beginners
  • Excel Intermediate
  • Advanced Excel For Experts

Lookup and Reference Examples

  • Multi-criteria lookup and transpose in Excel
  • Convert text string to valid reference in Excel using Indirect function
  • How to get last row in numeric data in Excel
  • How to get last row in text data in Excel
  • Count unique text values with criteria

Data Analysis Examples

  • How to create a Histogram in Excel
  • Conditional Formatting Icon Sets Examples in Excel
  • Excel Frequency Function Example
  • How to Create One and Two Variable Data Tables in Excel
  • How To Load Analysis ToolPak in Excel

Data Validation Examples

  • Excel Data validation specific characters only
  • How To Create Drop-down List in Excel
  • Excel Data validation date in next 30 days
  • Excel Data validation with conditional list
  • Excel Data validation allow weekday only

Next biweekly payday from date in Excel

by

To get the next payday – assuming a biweekly schedule, with paydays on Friday – you can use a formula based on the CEILING function.

 Formula

=CEILING(date+1,14)-1

Explanation

In the example shown, the formula in C6 is:

=CEILING(B6+1,14)-1

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

How this formula works

This formula depends on the CEILING 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.

Worked Example:   Round time to nearest 15 minutes in Excel

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.

Worked Example:   Excel MROUND, CEILING and FLOOR function examples

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:

=CEILING(A1+8,14)-8

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.

Worked Example:   CEILING function: Description, Usage, Syntax, Examples and Explanation

Post navigation

Previous Post:

How to calculate future date say 6 months ahead in Excel

Next Post:

How to calculate Next working/business day in Excel

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Learn Basic Excel

Ribbon
Workbook
Worksheets
Format Cells
Find & Select
Sort & Filter
Templates
Print
Share
Protect
Keyboard Shortcuts

Categories

  • Charts
  • Data Analysis
  • Data Validation
  • Excel Functions
    • Cube Functions
    • Database Functions
    • Date and Time Functions
    • Engineering Functions
    • Financial Functions
    • Information Functions
    • Logical Functions
    • Lookup and Reference Functions
    • Math and Trig Functions
    • Statistical Functions
    • Text Functions
    • Web Functions
  • Excel VBA
  • Excel Video Tutorials
  • Formatting
  • Grouping
  • Others
  • How to compare two text strings in Excel
  • Change Case: Uppercase, Lowercase, Propercase in Excel
  • LOWER function: Description, Usage, Syntax, Examples and Explanation
  • Two ways to Compare Text in Excel
  • How to use double quotes inside a formula in Excel
  • How to calculate next day of week in Excel
  • Count day of week between dates in Excel
  • Custom weekday abbreviation in Excel
  • Add workdays to date custom weekends in Excel
  • DATE function: Description, Usage, Syntax, Examples and Explanation
  • ACCRINT function: Description, Usage, Syntax, Examples and Explanation
  • How to calculate annuity for interest rate in excel
  • YIELDDISC function: Description, Usage, Syntax, Examples and Explanation
  • Calculate payment for a loan in Excel
  • Calculate interest for given period in Excel
Acronyms, Abbreviations, Initialism & What They Stand For
© 2021 xlsoffice. All Rights Reserved | Teal Smiles