How to Show 'Due' if Any Excel Date is Within 30 Days
Question details
The user needs a formula to check multiple non-contiguous date columns and return the text 'Due' if any of the dates fall strictly within the next 30 days, while ignoring past dates and dates that are 30 or more days away.
- Product
- Spreadsheet
- Device & OS
- not provided
- Scenario
- Tracking multiple client deadlines or upcoming events across different columns and automatically flagging the row when a deadline is approaching in the next 1 to 29 days.
- Observed behavior
- The user requires a logical formula to evaluate multiple separate date cells, calculate their difference from the current date, and output a specific status text based on the result.
Ensure that all your target cells are properly formatted as Dates and that your computer's system clock is accurate, as the TODAY() function relies on your device's current date setting.
Use Nested IF, OR, and AND Functions
This method individually tests each specific cell to see if the date is greater than today and less than 30 days away, combining the conditions with an OR function.
By combining the IF, OR, and AND functions, you can evaluate multiple non-adjacent columns at once. The AND function ensures that a specific date is both in the future and less than 30 days away. The OR function checks if any of the specified columns meet this criteria.
Click on the cell where you want the 'Due' or 'Up to Date' status to appear for the first row of your data.
Type the following formula into the formula bar, adjusting the cell references (M16, N16, T16, etc.) to match your actual date columns: =IF(OR(AND(M16-TODAY()>0,M16-TODAY()<30),AND(N16-TODAY()>0,N16-TODAY()<30),AND(T16-TODAY()>0,T16-TODAY()<30),AND(V16-TODAY()>0,V16-TODAY()<30),AND(X16-TODAY()>0,X16-TODAY()<30),AND(Z16-TODAY()>0,Z16-TODAY()<30)),"Due","Up to Date")
Press Enter to execute the formula. Then, click the small square at the bottom-right corner of the cell (the fill handle) and drag it down to apply the formula to the rest of your dataset.
Easily Manage Dates and Formulas with WPS Spreadsheet
WPS Spreadsheet fully supports advanced logical functions like IF, OR, AND, and TODAY(). It allows you to seamlessly track deadlines and automate status updates without any compatibility issues.
- 1. Open your workbook: Launch WPS Spreadsheet and open the document containing your client dates.
- 2. Start your formula: Click on the target status cell and type the '=' sign to begin building your formula.
- 3. Input the logic: Enter the nested IF, OR, and AND formula referencing your specific date cells.
- 4. Drag to fill: Press Enter, then use the drag-fill handle to copy the logic down your entire column.
Frequently Asked Questions
Why is my formula returning an error instead of 'Due'?
This usually happens if the referenced cells contain text instead of valid dates. Ensure your date columns are formatted correctly by selecting them, right-clicking, and choosing 'Format Cells' > 'Date'.
How can I include dates that are exactly 30 days away?
To include the 30th day in your 'Due' status, change the less-than operator (<30) to a less-than-or-equal-to operator (<=30) for each date check in your formula.
Can I highlight the 'Due' cells with a specific color automatically?
Yes, you can use Conditional Formatting. Select the column with your formula results, navigate to the 'Home' tab, click 'Conditional Formatting' > 'Highlight Cells Rules' > 'Text that Contains', and type 'Due' to apply a custom color fill.
