Formula Errors

How to Calculate Excel Formulas for the 15th and 30th of Each Month

Partner Editor Oct 8, 2026 868 views

Question details

The user needs an Excel formula to calculate twice-monthly payment dates strictly on the 15th and 30th without date drift.

How to Calculate Excel Formulas for the 15th and 30th of Each Month

Product: Excel
Device & OS: not provided
Scenario: Calculating semi-monthly schedules for payroll or recurring payments where simply adding 15 days causes the dates to drift off schedule.
Observed behavior: Adding a flat 15 days fails over time due to varying month lengths. Basic conditional formulas fail to advance to the next month properly or create invalid dates like February 30th.

Before you start

Ensure your starting date is correctly formatted as a date value in your spreadsheet, rather than as plain text, so the formulas can compute the days accurately.

Solution 1Recommended

Use Conditional Logic to Advance to the Next Payment Period

This is the most reliable method for generating a continuous schedule, as it evaluates the next period based on the previous date while correctly accounting for February.

A common mistake is using a formula that locks the output to the same month, preventing the schedule from advancing. Instead of relying purely on the current month, you should evaluate the next payment period by adding days (e.g., +15) to check when the month changes, or use conditional IF statements to handle end-of-month scenarios (like February 28/29).

Enter the starting date

Select cell A2 and enter your initial payment date, such as '1/15/2024'.

Input the advancing formula

In cell A3, enter the formula: =IF(DAY(A2)=15, IF(MONTH(A2)=2, EOMONTH(A2,0), DATE(YEAR(A2),MONTH(A2),30)), DATE(YEAR(A2),MONTH(A2)+1,15)).

Apply to the remaining schedule

Press Enter. Select cell A3, click and hold the fill handle at the bottom-right corner of the cell, and drag it down to automatically generate the rest of your semi-monthly schedule.

Use Conditional Logic to Advance to the Next Payment Period

Handling February correctly: This formula uses the EOMONTH function to automatically cap February at its last day (28th or 29th) instead of attempting to return an invalid 'February 30th'.

Solution 2

Round an Existing Date to the 15th or 30th

Use this method if you have a list of random dates that you need to snap to the closest 15th or 30th within the same month.

Easily Manage Payment Schedules in WPS Spreadsheet

WPS Spreadsheet provides powerful date and time functions to help you seamlessly calculate payroll and payment schedules. It fully supports advanced formulas, conditional formatting, and data validation to prevent date drift.

1. Open WPS Spreadsheet: Launch WPS Office and open a new or existing Spreadsheet document.
2. Apply the Date Formula: Enter your starting date in the first cell, then paste the semi-monthly calculation formula into the cell below.
3. Drag to Fill: Use the smart fill handle to drag down and generate your complete, error-free payment schedule in seconds.

100% compatible with Microsoft Excel formulas and date formatsBuilt-in EOMONTH, DATE, and IF functions for complex schedulingLightweight, fast, and completely free to useCross-platform availability on Windows, Mac, iOS, and Android

microsoft office alternative - wps office

Frequently Asked Questions

Why does adding 15 days to a date cause the schedule to drift?

Because months have varying lengths (28, 29, 30, or 31 days). If you add a flat 15 days to a 31-day month, your next date will land on the 16th or 1st instead of the 15th or 30th, causing the schedule to gradually shift over time.

How do I prevent Excel from creating a 'February 30th' error?

You can use the EOMONTH function to calculate the last day of a given month. By incorporating EOMONTH(A1, 0) for February dates, the formula will correctly return February 28th or 29th instead of attempting to output the 30th.

Can I use Data Validation to ensure correct payment dates?

Yes. You can select your date column, go to Data > Data Validation, choose 'Custom', and enter a formula like =OR(DAY(A1)=15, DAY(A1)=30, A1=EOMONTH(A1,0)). This prevents users from manually entering a date that does not align with your semi-monthly schedule.

Excel & Data Problems