Skip to content
Free Excel Tutorials
  • Home
  • Excel For Beginners
  • Excel Intermediate
  • Advanced Excel For Experts

Data Analysis

  • How to create Checklist in Excel
  • How To Compare Two Lists in Excel
  • Example of COUNTIFS with variable table column in Excel
  • How To Create Pareto Chart in Excel
  • How to calculate current stock or inventory in Excel

References

  • How to use Excel MATCH Function
  • Two-way lookup with VLOOKUP in Excel
  • How to get last row in text data in Excel
  • Count rows that contain specific values in Excel
  • How to use Excel FORMULATEXT function

Data Validations

  • Excel Data validation whole percentage only
  • Excel Data validation date in next 30 days
  • Excel Data validation require unique number
  • Excel Data validation must contain specific text
  • Excel Data validation unique values 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.

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:

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

Post navigation

Previous Post:

DECIMAL function: Description, Usage, Syntax, Examples and Explanation

Next Post:

AVERAGE function: Description, Usage, Syntax, Examples and Explanation

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

Logical Functions

  • IF with wildcards in Excel
  • IFERROR function: Description, Usage, Syntax, Examples and Explanation
  • Invoice status with nested if in Excel
  • IF, AND, OR and NOT Functions Examples in Excel
  • Excel If, Nested If, And/Or Criteria Examples

Date Time

  • NOW function: Description, Usage, Syntax, Examples and Explanation
  • Get last weekday in month in Excel
  • Basic Overtime Calculation Formula in Excel
  • Convert text date dd/mm/yy to mm/dd/yy in Excel
  • Count day of week between dates in Excel

Grouping

  • Map inputs to arbitrary values in Excel
  • Group numbers at uneven intervals in Excel
  • Group arbitrary text values in Excel
  • Group times into unequal buckets in Excel
  • If cell contains one of many things in Excel

General

  • How to Create Calendar in Excel
  • How to calculate percent sold in Excel
  • List worksheet index numbers in Excel
  • How to calculate percentage discount in Excel
  • 44 Practical Excel IF function Examples
© 2026 xlsoffice . All Right Reserved. | Teal Smiles | Abbreviations And Their Meaning