Automate Production Dates Using Closure Dates and Shelf Life in WPS Spreadsheets
Question details
The user needs to automatically calculate production dates by combining site closure dates with a product's required minimum shelf life.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- A production planner wants to replace manual date counting with formulas that calculate the latest production date from the customer delivery date, required shelf life, weekends, and site closure dates.
- Observed behavior
- Manual calculation is currently used and needs to be automated with working-day formulas.
Prepare a worksheet with columns for Product, Required Shelf Life in days, Customer Delivery Date, and Production Date. Create a separate list of site closure dates and convert your data to a table so formulas copy down automatically.
Calculate the Latest Production Date with WORKDAY.INTL
Use this formula when you want to count backward from the customer delivery date by the required shelf life while skipping weekends and site closure dates.
WORKDAY.INTL returns the date that is a specified number of working days before or after a start date. The negative number of days moves backward from the delivery date, and the weekend code 1 excludes Saturday and Sunday.
In WPS Spreadsheets, open your workbook and make sure columns A:D contain Product, Required Shelf Life, Customer Delivery Date, and Production Date.
Select the site closure dates range, go to the Formulas tab, choose Name Manager, and create the name ClosureDates.
In the first Production Date cell, enter =WORKDAY.INTL(C2, -B2, 1, ClosureDates) and press Enter. Adjust the cell references to match your table.
Drag the fill handle down the Production Date column to apply the calculation to all products.
Verify Remaining Shelf Life with NETWORKDAYS.INTL
Use this solution when you already have a production date and need to confirm that the working days between production and delivery meet the required shelf life.
Automate Production Date Calculation in WPS Spreadsheets
WPS Spreadsheets supports Excel-compatible working-day functions such as WORKDAY.INTL and NETWORKDAYS.INTL, so you can automate production dates, exclude site closure dates, and update the whole calculation when any input changes.
- 1. Create the calculation workbook: Open WPS Spreadsheets, click Blank worksheet, and enter Product, Required Shelf Life, Customer Delivery Date, and Production Date headings.
- 2. Prepare closure dates: Enter all site closure dates in a separate area, select them, go to Formulas > Name Manager, and define the name ClosureDates.
- 3. Apply the production date formula: In the first Production Date cell, type =WORKDAY.INTL(C2, -B2, 1, ClosureDates) and press Enter.
- 4. Fill the remaining rows: Use the fill handle to copy the formula down the Production Date column for all products.
- 5. Format the result as a date: Select the Production Date results, right-click, choose Format Cells, select Date, and click OK.
Frequently Asked Questions
Does WPS Spreadsheets support WORKDAY.INTL?
Yes. WPS Spreadsheets supports WORKDAY.INTL, including custom weekend codes and a holidays argument that accepts a range of closure dates.
How do I exclude site closure dates in the production date formula?
List the closure dates in a worksheet range, name it ClosureDates through Formulas > Name Manager, and use it as the last argument in WORKDAY.INTL or NETWORKDAYS.INTL.
What is the difference between WORKDAY.INTL and NETWORKDAYS.INTL?
WORKDAY.INTL returns a date that is a specified number of working days before or after a start date. NETWORKDAYS.INTL returns the number of working days between two dates.
What weekend code should I use for Saturday and Sunday?
Use weekend code 1 in WORKDAY.INTL or NETWORKDAYS.INTL to treat Saturday and Sunday as the weekend.
Why does my formula return a number instead of a date?
The cell is likely formatted as General or Number. Select the result cells, right-click, choose Format Cells, and apply the Date format.
