Return 1 or 0 in WPS Office Excel Based on Date Criteria
Question details
The user needs a formula to return 1 or 0 for visit dates by comparing each visit date with a specified start date, with the condition taking effect at the next visit.
- Product
- WPS Office Excel
- Device & OS
- not provided
- Scenario
- Comparing visit dates against a start date and outputting a numeric 1 or 0 result for each visit.
- Observed behavior
- The user wants the formula to return a numeric 1 or 0 instead of TRUE or FALSE based on the date comparison.
Verify that the visit dates and the reference start date are real Excel date cells rather than text; otherwise the comparison may not calculate correctly. Decide whether the condition should use < or <= based on whether the start date itself is included.
Use a double unary comparison to return 1 or 0 for a date condition
Use this compact formula when you need to convert the TRUE or FALSE result of a date comparison into a numeric 1 or 0.
WPS Office Excel evaluates A7<B2 as TRUE or FALSE. The double unary operator -- converts TRUE to 1 and FALSE to 0, so the result can be used directly in sums, counts, or other calculations.
Click the cell where you want the 1 or 0 result to appear.
Type =--(A7<B2) and press Enter, replacing A7 with the visit date cell and B2 with the start date cell.
Drag the fill handle down or across to apply the formula to the other visit dates.
Return 1 or 0 across a horizontal date range with TRANSPOSE
Use this when your visit dates are in a vertical range but the results need to be returned horizontally against horizontal criteria cells.
Create 1/0 Date Criteria Formulas in WPS Office Excel
In WPS Office Excel, use =--(A7<B2) or =--(TRANSPOSE(A7:A10)<B2:E2) to turn date comparisons into numeric 1/0 results. Open your workbook, select the result cell, enter the formula, and fill it across the visit dates.
- 1. Open WPS Office Excel: Launch WPS Office and open the workbook that contains your visit dates and start date.
- 2. Select a result cell: Click the cell where you want the 1 or 0 result to appear.
- 3. Enter the date comparison formula: Type =--(A7<B2) and press Enter to convert the date comparison result to a numeric 1 or 0.
- 4. Apply the formula to the remaining data: Use the fill handle or a TRANSPOSE array formula to extend the calculation to your other visit dates.
Frequently Asked Questions
What does the double unary -- do in =--(A7<B2)?
The double unary converts the TRUE or FALSE result of the date comparison into 1 or 0, so the result can be used in numeric calculations.
How do I return 1 if a visit date is after the start date in WPS Office?
Use =--(A7>B2) in WPS Office Excel, replace A7 with the visit date cell and B2 with the start date cell, then press Enter.
How can I compare a vertical date list with horizontal criteria cells?
Use =--(TRANSPOSE(A7:A10)<B2:E2) and confirm it as an array formula if required. This aligns the transposed visit dates with the horizontal date row.
Why does my date comparison return the wrong 1 or 0?
Check that all date cells contain real dates and not text, and confirm whether you need < or <= and whether an end date should also be included in the rule.
Does WPS Office support the same date formulas as Microsoft Excel?
Yes, WPS Office Excel supports the same comparison and TRANSPOSE formulas in .xlsx and .xls workbooks.
