Fix Excel #VALUE! Errors in IF, WORKDAY, VLOOKUP, and Date Formulas
Question details
The user needs to troubleshoot #VALUE! errors in procurement-date formulas that combine IF, WORKDAY, VLOOKUP, dates, durations, and separate holiday calendars.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Calculating local and international purchase dates or delivery dates with conditional WORKDAY formulas and lookup-based date values.
- Observed behavior
- One or more formulas return the #VALUE! error while comparable formulas in other cells may work.
Select each affected formula cell and press F2 to inspect its references. Check the source cells used for dates, durations, lookup results, and holiday lists, including cells that appear blank but may contain text or spaces.
Validate every date, duration, and holiday value used by the formula
Use this first when WORKDAY or date arithmetic returns #VALUE!, as the error commonly indicates text, blanks, or invalid values in an input range.
WORKDAY requires a valid start date and a numeric number of workdays. Its optional holiday range must contain real spreadsheet dates or blank cells; text labels, error values, and text-formatted date strings can cause a #VALUE! result.
Click the cell referenced as the WORKDAY start date, then choose Home > Number Format > Short Date. If the value remains left-aligned or does not display as a date, convert it with DATEVALUE or re-enter it as a date.
Open the formula cell with F2 and select the argument that supplies the number of days. Make sure that referenced cell contains a number, not text such as "10 days", an empty string (""), or an error value.
Select the holiday range used in WORKDAY, such as $H$2:$H$20, and verify that each nonblank entry is a valid date. Remove headings from the referenced range and correct cells containing text, spaces, or errors.
In an unused cell, enter a simplified test such as =WORKDAY(A2,5,$H$2:$H$20). If it returns a date, add the IF and VLOOKUP portions back one at a time to identify the failing argument.
Check IF branches and VLOOKUP results for compatible date values
Use this when the formula only fails for a certain purchase type, supplier, country, or lookup key.
Compare the working and failing formulas for incorrect references
Use this when a formula works in one column or row but returns #VALUE! in another, such as a working AS formula and failing AO or AK formulas.
Troubleshoot WORKDAY and Date Formula Errors in WPS Spreadsheets
WPS Spreadsheets lets you review procurement-date formulas, inspect cell references, test lookup outputs, and format source values as dates so you can isolate the input causing a #VALUE! error.
- 1. Open the affected workbook: Launch WPS Office, choose Spreadsheets, and open the .xlsx or .xls file containing the procurement-date calculation.
- 2. Inspect the failing formula: Select the cell showing #VALUE! and review the formula in the formula bar. Click each referenced cell or range to confirm the intended inputs.
- 3. Format source values as dates or numbers: Select the start-date, lookup-return, duration, and holiday cells, then use Home > Number Format to apply Date or Number as appropriate.
- 4. Test formula components: Enter the WORKDAY or VLOOKUP portion in an unused worksheet cell to identify whether the error comes from the date, holiday range, duration, or lookup result.
- 5. Correct references and recalculate: Update the incorrect cell or range reference, press Enter, and verify that the formula returns the expected workday date.
Frequently Asked Questions
Why does WORKDAY return #VALUE! when my start date looks correct?
The start date may be stored as text rather than a date serial number, or the days argument or holiday range may contain text or an error. Select each input cell and apply Home > Number Format > Short Date or Number to verify its stored value.
Can blank cells in a WORKDAY holiday range cause #VALUE!?
Truly empty cells are generally acceptable, but cells containing spaces, text labels, empty-text formulas such as "", or error values can cause problems. Keep only valid dates in the holiday range and exclude headings.
How can I tell whether VLOOKUP is returning a real date?
Enter the VLOOKUP formula in a separate cell and format that cell as Short Date. If it does not format as a date or returns an error, correct the lookup table value or convert a text date with DATEVALUE.
Why does the formula work in AS but fail in AO or AK?
Copied formulas can shift relative references to the wrong cells or holiday calendars. Use Formulas > Show Formulas and compare the AO, AK, and AS formulas, especially references that should remain fixed with $ signs.
Should local and international purchases use separate holiday ranges?
Yes. Create a clean date-only range for each calendar and reference the appropriate range in each WORKDAY branch, such as a local holiday range for local purchases and an international holiday range for international purchases.
