How to Auto-Fill the 15th and Last Day of Each Month in Excel
Question details
The user needs to generate a continuous date sequence in a spreadsheet that alternates exactly between the 15th day and the true last day of each month.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Creating bi-monthly timelines for payroll processing, billing schedules, or reporting periods where the end-of-month date dynamically shifts depending on the specific month (28th, 29th, 30th, or 31st).
- Observed behavior
- Excel's standard drag-to-fill feature applies a fixed interval (like adding 15 days), which fails to accurately calculate the fluctuating last day of different months, particularly February.
Verify that your starting cell is formatted as a Date and contains a valid starting date (e.g., the 15th of the current month) before applying the sequencing formulas.
Use a Combination of IF, EOMONTH, and DATE Functions
This formula intelligently checks the current day and alternates between finding the exact end of the current month and generating the 15th of the next month.
Standard auto-fill handles fixed intervals perfectly, but month lengths vary. By leveraging the EOMONTH (End of Month) function, the formula dynamically identifies whether a month ends on the 28th, 29th, 30th, or 31st.
The IF statement evaluates whether the previous date was the 15th or earlier. If it was, it outputs the last day of that month. If it was already the last day of the month, it outputs the 15th of the following month.
Select your starting cell (e.g., A1) and input your initial date, such as 15-Jan-2021.
Select the cell directly below it (e.g., A2) and enter the following formula: =IF(DAY(A1)<=15,EOMONTH(A1,0),DATE(YEAR(A1),MONTH(A1)+1,15))
Press Enter. Cell A2 will now calculate and display the true last day of the month for the date entered in A1.
Click on A2, grab the fill handle (the small square at the bottom-right of the cell border), and drag it down the column to generate the rest of your bi-monthly sequence automatically.
Use an Alternative MIN and EOMONTH Formula
A secondary mathematical approach utilizing MIN and EOMONTH to achieve the exact same alternating date sequence.
Easily Handle Complex Date Formulas with WPS Spreadsheet
WPS Spreadsheet fully supports advanced logical and date functions like IF, EOMONTH, and DATE, making it simple to build dynamic bi-monthly timelines. It provides a familiar interface and is entirely compatible with your existing workbooks.
- 1. Open your workbook: Launch WPS Spreadsheet and either open your existing .xlsx schedule or create a new blank workbook.
- 2. Input your initial date: Type your starting date, for example '15-Jan-2024', into cell A1.
- 3. Apply the sequence formula: In cell A2, type =IF(DAY(A1)<=15,EOMONTH(A1,0),DATE(YEAR(A1),MONTH(A1)+1,15)) and press Enter.
- 4. Drag the fill handle: Hover over the bottom-right corner of cell A2 until you see a crosshair, then drag it down to automatically populate the 15th and last days for the subsequent months.
Frequently Asked Questions
Why does standard auto-fill just add 15 days instead of finding the last day of the month?
Excel's default auto-fill increments dates by a fixed number of days based on the mathematical pattern of the first few selected cells. Because months irregularly contain 28, 29, 30, or 31 days, a fixed +15 day interval cannot dynamically adjust to the true end of the month, which is why a formula is necessary.
What exactly does the EOMONTH function do in Excel?
The EOMONTH (End of Month) function calculates and returns the serial number of the last day of the month that is a specified number of months before or after a starting date. Using EOMONTH(A1, 0) tells the program to find the exact last day of the same month as the date located in cell A1.
Why is my formula result showing as a random 5-digit number?
Spreadsheet programs store dates as serial numbers for mathematical calculations (for instance, 44226 represents January 30, 2021). If your formula cell isn't formatted to display as a Date, it will show this underlying number. You can fix this by right-clicking the cell, selecting 'Format Cells', and choosing your desired Date layout.
Will this formula automatically account for leap years in February?
Yes. By using the built-in EOMONTH function, the formula reads the specific year of your starting date and intrinsically knows whether that specific February ends on the 28th or the 29th without any manual adjustments.
