How to Create Boolean TRUE or FALSE Results from Excel Dates
Question details
The user needs to compare two date columns to output a Boolean TRUE or FALSE based on the specific number of days between them.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Calculating the difference between a newer date and an older date to categorize the gap into specific timeframes, such as over 60 days or between 30 and 60 days.
- Observed behavior
- The goal is to output TRUE or FALSE in corresponding columns based on whether the calculated date difference meets the specified numerical conditions.
Ensure that both columns containing your dates are formatted as Dates in your spreadsheet, rather than plain text, so the mathematical subtraction works correctly.
Use Logical Formulas to Compare Date Differences
Apply logical operators and the AND function to calculate the exact days between two dates and return a TRUE or FALSE result.
In Excel, dates are stored as sequential serial numbers, which means you can subtract an older date from a newer date to find the exact number of days between them. By pairing this subtraction with logical operators like greater than (>) or less than (<), Excel automatically outputs a Boolean TRUE or FALSE.
In your first target column (e.g., column M), enter the formula =K2-E2>60 to check if the newer date in K2 is more than 60 days older than E2.
In your second target column (e.g., column L), enter the formula =AND(K2-E2>30,K2-E2<=60). This ensures the difference is strictly greater than 30 days and less than or equal to 60 days.
Select the cells containing your newly entered formulas, click and hold the fill handle in the bottom-right corner of the selection, and drag it down to apply the formulas to the rest of your data rows.
Compare Dates and Generate Logical Results in WPS Spreadsheet
WPS Spreadsheet provides robust support for logical formulas, date formatting, and data analysis. You can seamlessly calculate date differences and generate Boolean TRUE/FALSE outputs within a clean, highly compatible interface.
- 1. Open your data: Launch WPS Spreadsheet and open the workbook containing your date columns.
- 2. Input the logical formula: Type =K2-E2>60 or =AND(K2-E2>30,K2-E2<=60) into your designated output cells.
- 3. Drag to fill: Double-click or drag the fill handle on the cell to copy the formula down to the bottom of your dataset.
Frequently Asked Questions
Why does my date comparison formula return a #VALUE! error?
This usually happens if one or both of the date cells are stored as text instead of an actual date. Select the problematic cells, navigate to the Home tab, and change the Number Format to Short Date.
How can I count the total number of TRUE results in my column?
You can use the COUNTIF function to tally them up. For instance, entering =COUNTIF(M2:M100, TRUE) will return the total count of TRUE values within that specific range.
Can I output custom text like 'Overdue' instead of TRUE or FALSE?
Yes, you can wrap your logical test inside an IF function to output custom text. For example, using =IF(K2-E2>60, "Overdue", "On Time") will replace TRUE with 'Overdue' and FALSE with 'On Time'.
