WPS Spreadsheet: Calculate Due Dates and Highlight Overdue Book Requests
Question details
The user needs to build a book-request tracker that calculates due dates, identifies overdue requests, and records request status with a drop-down list.
- Product
- WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- A book-request list contains the requested date, a fixed 14-day due period, and a status column for tracking fulfillment.
- Observed behavior
- The tracker needs formulas, conditional formatting, and data validation so due dates appear automatically, overdue requests are highlighted, and statuses can be selected consistently.
Confirm that the requested-date column contains real spreadsheet dates rather than text, and label the columns consistently—for example, Requested Date in C, Due Date in D, and Status in E. Decide whether the overdue highlight should apply only to due-date cells or to each complete request row.
Set up due dates, overdue highlighting, and a status list in WPS Spreadsheet
Use this method when every request has a 14-day due period and the tracker should update overdue formatting based on today’s date and the selected status.
The due-date formula adds 14 days to the requested date and leaves the cell blank when no date has been entered. Conditional formatting compares each due date with TODAY() and excludes requests marked Completed.
Apply the examples below to the first data row, then fill or copy the formula and validation settings down the tracker.
Select cell D2, choose the formula bar or press F2, and enter =IF(C2<>"",C2+14,""). Press Enter, then drag the fill handle down the Due Date column. Format the range as Date if the result displays as a serial number.
Select the due-date range, such as D2:D100, then open Home > Conditional Formatting > New Rule. Choose the formula-based rule option and enter =AND(D2<>"",D2<TODAY(),E2<>"Completed"). Select a noticeable fill color and click OK.
Select the Status range, such as E2:E100, then open Data > Data Validation. Choose List as the validation type and enter Sent, Ordered, Completed, Unavailable as the list values, separating items with commas.
Enter a past requested date in C2 and choose a status other than Completed in E2. Confirm that D2 shows a date 14 days later and that the due-date cell is highlighted; then select Completed and verify that the overdue highlight disappears.
Build an automated book-request tracker in WPS Spreadsheet
WPS Spreadsheet can calculate due dates with formulas, highlight overdue requests through conditional formatting, and provide controlled status choices with Data Validation. The familiar spreadsheet interface also makes it easy to save and share the finished tracker.
- 1. Create the tracker columns: Open WPS Spreadsheet and add headers such as Title, Requester, Requested Date, Due Date, and Status.
- 2. Calculate due dates: Select the first Due Date cell and enter =IF(C2<>"",C2+14,""); fill the formula down the column.
- 3. Highlight overdue requests: Use Home > Conditional Formatting > New Rule and apply =AND(D2<>"",D2<TODAY(),E2<>"Completed") to the due-date range.
- 4. Add status validation: Use Data > Data Validation, select List, and enter Sent, Ordered, Completed, Unavailable.
Frequently Asked Questions
How can I calculate a due date 14 days after the requested date?
In the first Due Date cell, such as D2, enter =IF(C2<>"",C2+14,"") and press Enter. Copy the formula down the column, then format the cells as dates if necessary.
How do I highlight only overdue requests in WPS Spreadsheet?
Select the due-date range, open Home > Conditional Formatting > New Rule, choose a formula rule, and use =AND(D2<>"",D2<TODAY(),E2<>"Completed"). Set the desired formatting and confirm the rule.
Why is a completed request still highlighted as overdue?
Check that the status cell contains exactly Completed and that the conditional-formatting formula references the correct status column. The rule excludes only cells whose value matches "Completed".
How do I create a status drop-down list?
Select the Status cells and open Data > Data Validation. Choose List, then enter Sent, Ordered, Completed, Unavailable as the allowed values.
Can I change the due period from 14 days to another number?
Yes. Replace 14 in the formula with the desired number of days, such as =IF(C2<>"",C2+30,"") for a 30-day period, and copy the revised formula down the column.
