Fix Excel DAYS #VALUE! Errors When Dates Are Stored as Text
Question details
The user needs to calculate days until expiration and make the Expiration Date formula return Best Before when Extended Shelf Life is NO, or remain visually blank when it is YES.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- An Excel table uses a formula to populate Expiration Date based on Extended Shelf Life, then uses DAYS to compare that date with today.
- Observed behavior
- DAYS returns #VALUE!. Some displayed dates may be text rather than numeric Excel dates, and the supplied IFS formula does not define a result when its condition is false.
Select a Best Before cell and an Expiration Date cell, then inspect their formulas in the formula bar. Confirm the intended date order, such as month/day/year or day/month/year, before converting text dates.
Identify text dates and convert them to real Excel dates
Use this approach when Best Before or Expiration Date contains text that Excel cannot interpret as a date.
Excel stores real dates as numbers. Changing a cell's display format to Date does not convert text into a numeric date value. DAYS can interpret some date text, but unrecognized date text produces #VALUE!.
In an empty cell, enter =ISNUMBER(A2), replacing A2 with a Best Before cell. Repeat for an Expiration Date cell. TRUE indicates a numeric value; FALSE means the value is not numeric and may be text or a blank result.
In a helper column, enter =DATEVALUE(A2) for a text date. If the result is a number, select the helper cell and use Home > Number Format > Short Date. Fill the formula down for the relevant text-date rows.
For a column of source text dates, select the cells and choose Data > Text to Columns. Proceed to the final wizard screen, select Date, choose the order matching the source text, such as MDY or DMY, and click Finish. Apply this to source values rather than cells whose formulas you need to retain.
If you used a helper column, copy its converted dates and use Home > Paste > Paste Special > Values to replace the corresponding source text dates. Confirm =ISNUMBER(A2) now returns TRUE, then recalculate the Expiration Date and DAYS formulas.
Correct the expiration formula and skip blank results in DAYS
Use this approach to return Best Before for NO and an empty string for YES, without passing the empty result to DAYS.
Calculate Days Until Expiration in WPS Spreadsheet
Use WPS Spreadsheet to convert recognizable text dates with DATEVALUE, return an expiration date with IF, and calculate days remaining with DAYS. Keep date values numeric and check for empty results before calculating.
- 1. Open the workbook: In WPS Spreadsheet, choose Menu > Open and select your Excel workbook. For this example, use C2 for Extended Shelf Life, D2 for Best Before, and E2 for Expiration Date.
- 2. Check and convert Best Before: Enter =ISNUMBER(D2) in an unused cell. If D2 contains recognizable date text, enter =DATEVALUE(D2) in a helper cell, then copy the result and use Paste Special > Values to place the numeric date in D2. Apply a Date format from the Home tab.
- 3. Set the expiration date: In E2, enter =IF(C2="NO",D2,""). This returns the numeric Best Before value for NO and an empty string otherwise. Apply a Date format to E2.
- 4. Calculate days remaining: In F2, enter =IF(E2="","",DAYS(E2,TODAY())). Use the fill handle to extend the formulas to additional rows, and format the days-remaining column as a number.
Frequently Asked Questions
Why does DAYS return #VALUE! even though the cells look like dates?
A displayed date may be text that Excel cannot interpret. Enter =ISNUMBER(A2) in an empty cell to check the source value. If it is text, try =DATEVALUE(A2), or use Data > Text to Columns > Date with the correct date order.
Why doesn't changing the cell format to Date fix the error?
Home > Number Format > Short Date changes how numeric values are displayed; it does not convert existing text to numbers. Convert the text with DATEVALUE or Text to Columns first, then apply the date format.
What is wrong with my IFS expiration-date formula?
The supplied formula has only one condition and result. When Extended Shelf Life does not match 'Value Settings'!B3, IFS returns #N/A. Use =IFS([@[Extended Shelf Life]]='Value Settings'!B3,[@[Best Before]],TRUE,"") to add a fallback, and make sure Best Before contains a numeric date.
How do I avoid a DAYS error when Expiration Date looks blank?
Check for an empty result before calling DAYS: =IF([@[Expiration Date]]="","",DAYS([@[Expiration Date]],TODAY())). This handles the empty string returned by the expiration formula.
What if DATEVALUE still returns #VALUE!?
The text may use a date order your regional settings do not recognize or contain extra characters. Inspect the value in the formula bar. For consistently ordered source dates, select the column and use Data > Text to Columns > Date, choosing the matching MDY, DMY, or YMD order.
