Formula Errors

Fix Excel IF Formula with AND and DATEVALUE Errors

Maira Mehtab Sep 27, 2026 869 views

Question details

The user needs to fix an Excel IF formula combining AND and DATEVALUE that constantly returns an incorrect value due to text formatting or regional setting conflicts.

Product: Excel
Device & OS: not provided
Scenario: Using an IF function with AND conditions to check if a date falls within a specific range.
Observed behavior: The formula always returns the false value (50) because the referenced date is stored as text, its format is incorrectly interpreted, or DATEVALUE clashes with the system's regional settings.

Before you start

Verify that the target cell (e.g., I4) contains a valid Excel date and is not stored as text. You can test this by changing the cell's number format to 'General' to see if it converts to a numeric serial value.

Solution 1Recommended

Replace DATEVALUE with the DATE Function

Using the DATE function avoids regional setting conflicts that often occur with DATEVALUE by explicitly specifying the exact year, month, and day.

The DATEVALUE function relies heavily on your computer's regional settings to interpret text dates, which can easily cause unexpected results or errors when sharing files. Using the DATE function explicitly defines the year, month, and day, ensuring the formula works universally.

Select the formula cell

Click on the cell where your current IF formula is located.

Update the formula

Replace the DATEVALUE segments with DATE. Enter `=IF(AND(I4>=DATE(2024,8,3),I4<=DATE(2024,8,5)),0,50)`.

Apply the result

Press Enter to apply the formula. It will now return 0 if the date in I4 falls between August 3 and August 5, 2024, and 50 otherwise.

Date Format: Ensure cell I4 is formatted as a Date. If it remains text, you may need to use the 'Text to Columns' feature in the Data tab to convert it to a valid date.

Solution 2

Use a Boolean Arithmetic Alternative

This alternative bypasses the IF and AND functions entirely by using boolean logic and mathematical operations to evaluate dates.

WPS Spreadsheet Pro Tips

Handle Complex Date Formulas with WPS Spreadsheet

WPS Spreadsheet provides robust formula support and error-checking tools, making it easy to troubleshoot and correctly format IF, AND, and DATE formulas without regional syntax issues.

1. Open your workbook in WPS Spreadsheet: Launch WPS Office and open your .xlsx file containing the date formula.
2. Check date formats: Highlight the date cells, right-click, and select 'Format Cells' to ensure they are set to 'Date' instead of 'Text'.
3. Apply the DATE formula: Type `=IF(AND(I4>=DATE(2024,8,3),I4<=DATE(2024,8,5)),0,50)` in your desired cell and press Enter.
4. Drag to fill: Use the fill handle at the bottom right of the cell to drag the formula down to apply it to the rest of your data.

100% compatible with Microsoft Excel formulas (.xlsx)Built-in error checking for date values and logic errorsIntuitive formula builder and syntax highlightingFree to download and use

Frequently Asked Questions

Why does DATEVALUE cause formula errors in Excel?

DATEVALUE converts a date stored as text into a serial number, but it heavily depends on your system's regional date settings (e.g., MM/DD/YYYY vs. DD/MM/YYYY). If the text format doesn't match the system settings, it evaluates incorrectly or throws a #VALUE! error.

How do I check if my date is stored as text?

Select the cell containing the date and change the number format to 'General' from the Home tab. If the date turns into a 5-digit number (like 45507), it is a valid Excel date. If it stays looking like a date, it is stored as text.

Can I use DATEVALUE if my dates are consistently formatted as text?

Yes, but it's risky if the file is shared internationally. If you must use it, ensure the text date strictly matches your region's short date format, for example: DATEVALUE("8/3/2024"). It is generally safer to use the DATE function.

Excel & Data Problems