How to Return Yes or No for Patient Dates Within 30 Days in WPS Spreadsheets
Question details
The user needs a WPS Spreadsheets formula that checks dates for the same patient ID and returns Yes or No when the dates fall within a 30-day period, based on a defined comparison rule.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- The user is tracking patient visit dates in a spreadsheet and wants to flag records that are within 30 days for each patient.
- Observed behavior
- Goal state: a formula column shows Yes when the patient's date meets the selected 30-day condition and No otherwise.
Decide whether each date should be compared with the patient's earliest date, latest date, or any other date. Arrange your data with Patient ID in one column and Date in the next column, and make sure the date column is formatted as Date.
Compare each patient date with their earliest date using MINIFS
Use this method when a visit should be flagged if it occurs on or within 30 days after the patient's first recorded date.
This formula uses MINIFS to find the earliest date for each patient ID, then checks whether the row date is equal to that earliest date or up to 30 days later.
In WPS Spreadsheets, place Patient ID in column A and Date in column B, with the first data row in row 2.
Type =IF(AND(B2>=MINIFS($B$2:$B$100,$A$2:$A$100,A2),B2<=MINIFS($B$2:$B$100,$A$2:$A$100,A2)+30),"Yes","No") and press Enter.
Drag the fill handle from C2 down through all patient rows to apply the formula.
Compare each patient date with their latest date using MAXIFS
Use this method when a visit should be flagged if it occurs on or within 30 days before the patient's most recent recorded date.
Compare with any other patient date within 30 days using COUNTIFS
Use this method when a row should be Yes if there is at least one other date for the same patient within 30 days before or after it.
Flag Patient Dates Within 30 Days in WPS Spreadsheets
Build a Yes/No date check directly in WPS Spreadsheets using MINIFS, MAXIFS, or COUNTIFS. WPS Spreadsheets uses Excel-compatible formulas and works with .xlsx patient workbooks, so you can get the same result without Microsoft Excel.
- 1. Download WPS Office and open WPS Spreadsheets: Download WPS Office from the official WPS site, install it, and open WPS Spreadsheets.
- 2. Open the patient workbook: Click Menu > Open to open your patient workbook, or start a new spreadsheet and enter Patient ID and Date columns.
- 3. Enter the comparison formula: Select the result cell and enter the MINIFS, MAXIFS, or COUNTIFS formula that matches your chosen date rule.
- 4. Fill the formula down: Drag the fill handle from the result cell down through all patient rows.
- 5. Verify the Yes/No results: Spot-check rows to confirm the formula returns Yes only for dates that satisfy the selected 30-day condition.
Frequently Asked Questions
Can I compare each date to the patient's earliest date?
Yes. Use MINIFS to get the earliest date for that patient ID, and return Yes when the row date is between the earliest date and earliest date plus 30 days.
What formula shows Yes if any two dates for the same patient are within 30 days?
Use COUNTIFS with the patient ID and a date range from B2-30 to B2+30. If the count of matching dates is greater than 1, return Yes; otherwise return No.
Why does my MINIFS formula mark the first patient date as Yes?
Because the earliest date is included in the condition. If you want to exclude the first visit, change the comparison from B2>=MINIFS(...) to B2>MINIFS(...).
Does WPS Spreadsheets support these formulas when opening Excel files?
Yes. WPS Spreadsheets supports MINIFS, MAXIFS, and COUNTIFS and saves .xlsx files that remain compatible with Microsoft Excel.
