WPS Spreadsheet: Set an Excel Date to the 10th or 25th Based on Another Date
Question details
Set a calculated date to the 25th or 10th based on the day of another date.
- Product
- WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- A source date is stored in cell A1, and the user needs a formula that assigns dates according to whether the source day falls before the 10th, from the 10th through the 24th, or on or after the 25th.
- Observed behavior
- The desired result is the 25th of the current month for source dates from the 10th through the 24th, the 10th of the current month for earlier dates, and the 10th of the following month for dates on or after the 25th.
Enter a valid date in cell A1 and select an empty cell for the calculated result. Confirm that A1 is recognized as a date rather than text so DAY, MONTH, and YEAR can evaluate it correctly.
Use an IF and DATE formula to assign the 10th or 25th
Use this formula when the source date is in A1 and the result should follow the three day-range rules automatically.
The formula checks the day of A1 in order: days 10 through 24 return the 25th of the same month, days before 10 return the 10th of the same month, and days 25 or later return the 10th of the next month. DATE also handles the change from December to January automatically.
In WPS Spreadsheet, click the empty cell where you want the calculated date to appear.
Type =IF(AND(DAY(A1)>=10,DAY(A1)<=24),DATE(YEAR(A1),MONTH(A1),25),IF(DAY(A1)<10,DATE(YEAR(A1),MONTH(A1),10),DATE(YEAR(A1),MONTH(A1)+1,10))) and press Enter.
Select the result cell, open the Home tab, choose the Number Format dropdown, and select Date or a custom date format.
If each row has a source date in column A, drag the fill handle from the formula cell down to copy the formula to the remaining rows.
Calculate Monthly 10th and 25th Dates in WPS Spreadsheet
WPS Spreadsheet lets you enter the conditional DATE formula, apply a readable date format, and fill the calculation across your worksheet while maintaining Excel-compatible formulas and .xlsx files.
- 1. Open WPS Spreadsheet: Launch WPS Office and open an existing workbook or create a new spreadsheet.
- 2. Add the source date: Enter the source date in cell A1, or place your date data in column A.
- 3. Insert the date formula: Select an output cell and enter the IF, AND, DAY, DATE, YEAR, and MONTH formula for the required date rule.
- 4. Format and fill the results: Use Home > Number Format > Date, then copy the formula down for additional source dates.
Frequently Asked Questions
What formula returns the 25th for dates from the 10th through the 24th?
Use =IF(AND(DAY(A1)>=10,DAY(A1)<=24),DATE(YEAR(A1),MONTH(A1),25),IF(DAY(A1)<10,DATE(YEAR(A1),MONTH(A1),10),DATE(YEAR(A1),MONTH(A1)+1,10))). Replace A1 with the cell containing your source date.
What happens when the source date is on the 25th or later?
The formula returns the 10th of the following month by using MONTH(A1)+1 in the final DATE function.
Why does the result display as a number instead of a date?
The cell is likely using General or Number formatting. Select the result cell, go to Home > Number Format, and choose Date or apply a custom date format.
Can this formula handle a date in December?
Yes. DATE(YEAR(A1),MONTH(A1)+1,10) automatically rolls a December result into January of the next year.
How can I use the formula for dates in several rows?
Place each source date in column A, enter the formula in the corresponding output cell, and drag the fill handle downward. Relative references such as A1 will adjust to A2, A3, and so on.
