Function Problems

Excel Formula to Assign Month and Year by Custom Date Range

Maira Mehtab Sep 22, 2026 869 views

Question details

The user needs to categorize dates into custom reporting months, where a new month starts on the 11th of the current month and ends on the 10th of the following calendar month.

Product: Microsoft Excel
Device & OS: not provided
Scenario: Creating custom reporting periods or accounting months for data analysis, financial tracking, or payroll organization.
Observed behavior: A formula is required to automatically shift dates based on a 10-day offset and output the assigned period in an mm/yyyy format.

Before you start

Ensure that the source cells containing your dates are formatted as actual date values rather than text strings, otherwise the formula will return an error.

Solution 1Recommended

Use the EOMONTH Function to Shift Dates

Applying a formula with the EOMONTH function allows you to subtract days to shift the start of the month, effectively adjusting your reporting period to match a custom cycle.

By subtracting 10 days from the target date, any date from the 1st to the 10th falls into the previous month's calculation, while the 11th onwards remains in the current month. The EOMONTH function then finds the end of the previous month, and adding 1 day resets it to the first day of the correct reporting month.

Enter the EOMONTH Formula

Select the cell where you want the custom reporting period to appear. Type the formula =EOMONTH(A2-10,-1)+1 (assuming your source date is in cell A2) and press Enter.

Open the Format Cells Dialog

Right-click the cell containing the new formula result and select 'Format Cells' from the context menu, or press Ctrl+1 on your keyboard.

Apply Custom Formatting

Navigate to the 'Number' tab, select 'Custom' from the category list, and type mm/yyyy in the 'Type' input box. Click OK to apply the formatting.

Understanding the Formula Offset: The A2-10 component offsets the date backwards by 10 days. The -1 in the EOMONTH function moves back one full month from that shifted date, and the +1 ensures all adjusted dates land uniformly on the 1st of your target reporting month.

Advanced Spreadsheet Features

Calculate Custom Date Ranges Easily with WPS Office

WPS Spreadsheet provides full support for advanced date and time functions, including EOMONTH, enabling you to manage custom reporting periods and accounting cycles effortlessly.

1. Open your data in WPS Spreadsheet: Launch WPS Office and open your workbook containing the original date records.
2. Apply the EOMONTH formula: In an empty cell next to your date, enter =EOMONTH(A2-10,-1)+1 and hit Enter to calculate the adjusted date.
3. Apply Custom Date Formatting: Press Ctrl+1 to open the Format Cells dialog, select Custom, enter mm/yyyy, and click OK.

100% compatibility with Microsoft Excel formulas and formatting rules.Free, lightweight, and fast-loading spreadsheet editor for everyday tasks.Built-in custom cell formatting for precise date and financial representations.Cross-platform support allowing you to manage data on Windows, Mac, and mobile.

microsoft office alternative - wps office

Frequently Asked Questions

Why does the EOMONTH formula return a 5-digit number instead of a date?

Spreadsheet software stores dates as sequential serial numbers. If your formula returns a number like 45220, it is calculating correctly but lacks formatting. Simply right-click the cell, choose 'Format Cells', and apply a Date or Custom format.

How can I change the formula if my reporting period starts on the 15th of the month?

You can easily adjust the day offset in the formula to match your custom cycle. To start a period on the 15th, you need to subtract 14 days. Change the formula to =EOMONTH(A2-14,-1)+1.

Can I use the TEXT function to format the month and year directly within the formula?

Yes, you can wrap the formula in a TEXT function to output text directly without needing to change cell formatting rules. Use =TEXT(EOMONTH(A2-10,-1)+1, "mm/yyyy") to get the formatted text result immediately.

Excel & Data Problems