Function Problems

How to Round Dates to the 15th or Last Day of the Month in Excel

Maira Mehtab Oct 16, 2026 869 views

Question details

The user needs an Excel formula to standardise dates by returning the 15th day for any date before the 16th, and the last day of the month for dates falling on or after the 16th, while correctly handling varying month lengths and leap years.

How to Round Dates to the 15th or Last Day of the Month in Excel

Product: Excel
Device & OS: not provided
Scenario: Standardizing cycle dates for bi-monthly financial periods, payroll processing, or billing workflows.
Observed behavior: The correct formula evaluates the date and outputs '15' for the first half of the month, and the exact final day number (28, 29, 30, or 31) for the second half.

Before you start

Verify that your source cells contain correctly formatted Excel dates rather than text strings, as date functions like DAY and EOMONTH will only operate on valid numeric date values.

Solution 1Recommended

Use IF, DAY, and EOMONTH to Extract the Day Number

This method evaluates the day of the month and dynamically outputs either 15 or the accurate last day of the given month, accurately accounting for February and leap years.

By combining the logical IF function with the EOMONTH (End of Month) function, you can automatically calculate complex calendar scenarios without needing to manually list out the days in each month.

Select the target cell

Click on the empty cell where you want the calculated day number to appear.

Enter the nested formula

Type the formula: =IF(ISNUMBER(S6)=FALSE,"N/A",IF(DAY(S6)<16,15,DAY(EOMONTH(S6,0)))) into the formula bar. Replace 'S6' with the cell reference that contains your original date.

Apply the result

Press Enter to execute the formula. If the date in S6 is before the 16th, it will return 15. If it is the 16th or later, it will return the last day of that month.

Copy down the column

Hover over the bottom-right corner of the cell until the fill handle appears, then drag it down to apply the formula to the rest of your dataset.

Use IF, DAY, and EOMONTH to Extract the Day Number

Understanding the EOMONTH Function: The EOMONTH function automatically identifies the correct last day of any month, which ensures that months with 30 or 31 days, as well as February 28th or 29th, are processed perfectly without additional manual logic.

Solution 2

Alternative: Return a Full Date Format

Use this variation if your workflow requires the output to be an actual calendar date (e.g., MM/DD/YYYY) rather than just a day integer.

Manage Data with WPS Spreadsheet

Easily Process Complex Date Formulas in WPS Spreadsheet

WPS Spreadsheet fully supports advanced date and time functions, including nested IF statements and EOMONTH. You can perfectly execute standardizing workflows and complex calculations with excellent performance.

1. Open your file in WPS Office: Launch WPS Spreadsheet and open the document containing your date records.
2. Access the Formulas tab: Navigate to the Formulas tab on the top ribbon and select Date & Time functions if you need guidance with syntax.
3. Insert the logic: Paste the required IF and EOMONTH formula into your designated target cell.
4. Save your progress: Save your completed spreadsheet seamlessly in the universal .xlsx format for easy sharing.

100% compatible with Microsoft Office Excel formats (.xlsx) and standard formula logic.Built-in function wizard helps easily construct complex date and logical operations without syntax errors.Lightweight and fast, ensuring smooth data processing even when working with thousands of payroll or billing records.

microsoft office alternative - wps office

Frequently Asked Questions

Why does my formula return 'N/A'?

The formula includes an ISNUMBER function that acts as an error-handling check. It returns 'N/A' if the target cell does not contain a recognized numeric date value. To fix this, ensure your original dates are formatted correctly as Dates and not as plain text strings.

What does the '0' mean in the EOMONTH(S6,0) function?

The '0' specifies the number of months to move forward or backward from the referenced date. By using '0', you instruct the formula to find the last day of the exact same month as the date located in cell S6.

Can I adapt this to round to the 10th and 25th of the month instead?

Yes. You can simply alter the logic thresholds. For example, to round to the 10th for dates before the 11th, and the 25th for dates after, you can adjust the formula conditions to check IF(DAY(S6)<11, 10, ... ) and modify the remaining logic based on your exact cycle needs.

Excel & Data Problems