Create an Excel Delivery Run Sheet by Frequency and Date in WPS Office
Question details
The user needs a daily delivery run sheet that identifies clients due for delivery on a selected date, based on weekly or fortnightly frequency and a scheduled day of the week.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- A delivery schedule contains client names, delivery frequency, delivery day, and a selected run-sheet date. The user wants to avoid manually filtering the list each week.
- Observed behavior
- Manual filtering is still required to determine which clients are due on the selected date; the goal is to return matching clients automatically.
Prepare your delivery data in WPS Spreadsheets with columns for Client Name, Last Delivery Date, Delivery Frequency, and Delivery Day. Enter the selected run-sheet date in a dedicated cell, such as G2, so the formulas can reference it.
Use a Due-Date Formula and AutoFilter to List Matching Deliveries
Use this formula-based method when you want a reusable run-sheet workbook that updates automatically whenever the selected delivery date changes.
The helper column checks two conditions: the selected run-sheet date must match the client's delivery day, and the number of days since the last delivery must be divisible by the delivery interval (7 for weekly, 14 for fortnightly).
Launch WPS Office Spreadsheets and open the workbook that contains the delivery schedule. Make sure column B contains the last delivery date, column C contains Weekly or Fortnightly, and column D contains the weekday name such as Monday. In cell G1, type Run-Sheet Date, and in cell G2 enter the delivery date you want to check, such as 2025-05-19.
Click cell E1 and type Due. In cell E2, enter the formula =AND(TEXT($G$2,"dddd")=D2,MOD($G$2-B2,IF(C2="Weekly",7,14))=0) and press Enter.
Select cell E2, move the pointer to the bottom-right fill handle, and double-click to fill the formula down the column. Cells showing TRUE are due for delivery on the selected date.
Select the header row A1:E1. On the Data tab, click AutoFilter. Click the filter arrow in the Due column, clear Select All, select TRUE, and click OK.
Select the visible rows, copy them, and paste them into a new sheet named Run Sheet. Format the columns as required, then save, print, or share the run sheet.
Create an Automatic Delivery Run Sheet in WPS Office Spreadsheets
WPS Office Spreadsheets gives you the formulas, AutoFilter, and Excel-compatible tools to build a date-based delivery list without VBA. Change one date cell to see which clients are due, then save or share the run sheet as .xlsx or PDF.
- 1. Download WPS Office: Get WPS Office from the official WPS website and install it on your Windows, macOS, iOS, or Android device.
- 2. Open your delivery workbook: Launch WPS Office Spreadsheets and open the Excel workbook containing client name, frequency, delivery day, and last delivery date.
- 3. Enter the run-sheet date: Type the selected delivery date in a dedicated cell such as G2.
- 4. Apply the due formula and filter: Add the TRUE/FALSE helper column using the frequency and date formula, then use Data > AutoFilter to show only TRUE rows.
- 5. Save or share the run sheet: Save the generated list as .xlsx or PDF, then print or share it with your delivery team.
Frequently Asked Questions
Can I make the delivery list update automatically when I change the selected date?
Yes. Place the selected date in a single cell and use a formula that returns TRUE for due deliveries. When you change that date, the helper column recalculates, and you can refresh the AutoFilter to show the new matching rows.
What should I include in the delivery schedule workbook?
Include at least Client Name, Last Delivery Date, Delivery Frequency such as Weekly or Fortnightly, and Delivery Day such as Monday. Use the delivery day and the number of days since the last delivery to calculate whether a client is due.
Does this method work for fortnightly schedules?
Yes. The formula divides the days between the last delivery and the selected date by 14 for Fortnightly clients and by 7 for Weekly clients. If the remainder is zero, the client is due on that date.
Can I use this workbook in Microsoft Excel?
Yes. The same workbook and formulas work in WPS Office Spreadsheets and Microsoft Excel. Save the file in .xlsx format for compatibility when sharing with Excel users.
Do I need VBA or macros to create the run sheet?
No. You can create the due-date logic with standard spreadsheet formulas and AutoFilter. VBA or Power Query can be used for a more automated refreshable output, but they are not required.
