Date & Time
Next Biweekly Payday from Any Date formula (14-Day Anchor + CEILING.MATH)
Find the next date on or after a query date in a fixed 14-day schedule anchored to a known date.
A fixed 14-day cycle, not a twice-monthly schedule
Every scheduled date is AnchorDate + 14 × k, including dates before the anchor. The anchor determines the weekday; Friday is specific to this example. A calendar year can contain 26 or 27 occurrences, depending on the schedule and year.
This model does not adjust dates for weekends, public holidays or bank processing. It is a calendar exercise rather than a complete payroll policy. Twice-monthly schedules need different logic.
Both inputs must be numeric spreadsheet dates. INT discards time-of-day fractions so a query at noon on a scheduled date still returns that same day. These examples accept calendar dates from 1901 through 9998 to keep the next occurrence within the supported date range. DATE constructs the seed values without relying on text-date parsing.
| Query Date (Col B3) | Days Since 2026-01-02 Anchor | Next Biweekly Payday | Days Remaining |
|---|---|---|---|
| 2026-01-07 (Wed) | +5 days (0.36 cycles) | 2026-01-16 (Fri) | 9 days |
| 2026-01-30 (Exact Payday!) | +28 days (2.00 cycles) | 2026-01-30 (Today!) | 0 days |
| 2026-01-31 (Sat after payday) | +29 days (2.07 cycles) | 2026-02-13 (Fri) | 13 days |
Next Biweekly Payday On or After Query Date B3
CEILING.MATH rounds the elapsed cycle count toward the next scheduled date, including negative counts before the anchor. TEXT returns a display string, not a date serial.
=IF(COUNT(B2:B3)<>2,"Enter numeric dates",IF(OR(MIN(B2:B3)<DATE(1901,1,1),MAX(B2:B3)>=DATE(9999,1,1)),"Date outside example range",TEXT(INT(B2)+CEILING.MATH((INT(B3)-INT(B2))/14,1)*14,"yyyy-mm-dd")))Countdown Days Remaining Until Biweekly Payday (0 on Payday)
The countdown is a whole number from 0 through 13. An exact scheduled date returns zero.
=IF(COUNT(B2:B3)<>2,"Enter numeric dates",IF(OR(MIN(B2:B3)<DATE(1901,1,1),MAX(B2:B3)>=DATE(9999,1,1)),"Date outside example range",MOD(14-MOD(INT(B3)-INT(B2),14),14)))Keep the result as a numeric date
Use this version for later date arithmetic and apply a date display format to its cell.
=IF(COUNT(B2:B3)<>2,"Enter numeric dates",IF(OR(MIN(B2:B3)<DATE(1901,1,1),MAX(B2:B3)>=DATE(9999,1,1)),"Date outside example range",INT(B2)+CEILING.MATH((INT(B3)-INT(B2))/14,1)*14))Try the example
Use the original values or change them to test your understanding.