WPS Spreadsheet Formula to Mark Dates in the First Week of a Month
Question details
The user needs a formula that checks several date rows and returns X for dates occurring from the first through the seventh day of the month, and Z for all other dates.
- Product
- Microsoft Excel or WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- A range such as A1:A5 contains dates, and each row must be evaluated to produce a combined result such as X,Z,Z,Z,Z.
- Observed behavior
- The required output is X for dates on days 1–7 and Z for dates outside that range.
Verify that the source cells contain actual date values rather than text strings. Identify the date range, such as A1:A5, before entering the formula.
Use a row-by-row formula and combine the results
Use this compatible approach when you need to evaluate each date separately and combine the X and Z results into one cell.
The DAY function extracts the day of the month. Since valid dates always have a day from 1 through 31, checking whether DAY is less than 8 identifies dates from the first through the seventh day.
In a blank cell beside the first date, such as B1, enter =IF(AND(DAY(A1)>=1,DAY(A1)<=7),"X","Z") and press Enter.
Select B1 and drag the fill handle down through B5, or copy B1 and paste it into B2:B5. Each row will return X or Z based on its date.
In a separate result cell, enter =TEXTJOIN(",",TRUE,B1:B5) and press Enter to produce a result such as X,Z,Z,Z,Z.
Use a dynamic array formula for Microsoft 365 or Excel for the web
Use this compact option when the spreadsheet supports BYROW, LAMBDA, and dynamic array formulas.
Evaluate First-Week Dates Easily in WPS Spreadsheet
WPS Spreadsheet can classify dates with a standard IF, AND, and DAY formula, fill the formula across a range, and combine the results with TEXTJOIN. Its familiar worksheet interface makes it easy to migrate and work with compatible Excel files.
- 1. Open the spreadsheet: Launch WPS Spreadsheet and open the workbook containing the date range, such as A1:A5.
- 2. Add the row formula: In B1, enter =IF(AND(DAY(A1)>=1,DAY(A1)<=7),"X","Z") and press Enter.
- 3. Fill the classification down: Copy B1 or drag its fill handle through B5 to evaluate every date row.
- 4. Combine the results: In a blank cell, enter =TEXTJOIN(",",TRUE,B1:B5) to display the classifications as a single result.
Frequently Asked Questions
What formula returns X for dates from the first through seventh day of the month?
Use =IF(AND(DAY(A1)>=1,DAY(A1)<=7),"X","Z") in the row next to the date. Replace A1 with the cell containing the date, then fill the formula down.
How can I combine the X and Z results into one cell?
After evaluating each row in B1:B5, enter =TEXTJOIN(",",TRUE,B1:B5) in a blank cell. This returns a comma-separated result such as X,Z,Z,Z,Z.
Why does the formula give an incorrect result for a date?
The source value may be stored as text instead of as a real date. Select the cell and inspect the formula bar, then convert the value to a valid date before applying the DAY function.
Can I use the BYROW and LAMBDA formula in every spreadsheet version?
No. =TEXTJOIN(",",TRUE,BYROW(A1:A5,LAMBDA(x,IF(AND(DAY(x)>=1,DAY(x)<=7),"X","Z")))) requires Microsoft 365, Excel for the web, or a spreadsheet version that supports BYROW and LAMBDA. The row-by-row IF formula is more broadly compatible.
