OmniDocFix Excel Date Formulas That Are Not Working
Date formulas in Excel often fail because dates are stored as text or in the wrong order. Learn how to check cell types and convert dates correctly.
If your Excel formulas for dates is not working, the formula is often not the problem. A date formula may return #VALUE!, show a number like 45321, or give a result that is off by a month. The formula looks right, the cells look right, yet the answer is wrong. In many cases Excel is treating your dates as text, or it read them in a different order than you meant.
This guide is for anyone who works with date columns, including people who pasted dates from a web page, exported them from a report, or pulled them out of a PDF table. Common fixes for formulas that will not calculate, such as manual calculation mode or broken references, do not address this problem. The focus here is on dates that look correct on screen but are stored as the wrong kind of data.
By the end, you will know how to confirm whether a cell holds a real date, how to convert text dates, and how to keep new entries clean.
Why are my Excel formulas for dates not working? The short answer
Excel can only calculate with dates that are stored as real date values, so convert any text dates before you change the formula, and check the day and month order against the source.
| Your situation | What to do | Why |
|---|---|---|
| Dates are left-aligned and formulas return #VALUE! | Convert the column to real dates by rebuilding each one with DATE | Text cannot be added to or subtracted from as a date |
| Results show numbers like 45321 | Apply a date number format to the formula cells | Excel stores dates as serial numbers; the math is correct, only the display is wrong |
| Day and month are swapped after import | Split the text into parts and rebuild with DATE(year, month, day) | Regional settings decide whether 03/04 means March 4 or April 3 |
| A date is one day off when times are included | Wrap the date in INT to drop the time portion | A timestamp adds a fraction of a day to the serial number |
| A date looks right but formulas skip it | Remove hidden spaces with TRIM and SUBSTITUTE | Extra characters stop the text from matching or converting |
Figure out which problem you have
Several different causes produce similar symptoms. Use this table to match what you see with the likely cause, then run the check in the last column before you change anything.
| Symptom | Likely cause | How to confirm |
|---|---|---|
| #VALUE! error in date arithmetic | The cell holds text, not a date | Enter =ISNUMBER(A2) in a spare column and fill down; FALSE means text |
| A five-digit number appears instead of a date | The formula cell uses a General or Number format | Select the cell and check its format; a date format fixes the display |
| 03/04/2024 gives a different date than expected | Day and month order does not match the source | Look for a row where the first number is above 12; if it sits in the day position, the source is day first |
| The date is one day off | The cell includes a time | Compare =A2 with =INT(A2); if the two values differ, a time is present |
| A date looks normal but formulas ignore it | Hidden spaces or non-breaking spaces from a web page or PDF | Compare =LEN(A2) with the number of visible characters, then test with ISNUMBER |
| A year shows up as 1925 or 2025 unexpectedly | A two-digit year was read by Excel's default century rule | Check the original text for two-digit years |
Fix it step by step
The fixes differ by cause, so work through the section that matches your symptom. Start with the text-date fix, since it resolves many common causes in one pass.
Fix text dates
- Add a helper column next to your dates and enter =ISNUMBER(A2). Fill it down. Each FALSE row is text that needs conversion.
- Remove hidden characters in a second helper column with =TRIM(SUBSTITUTE(A2," ",""))-style cleanup. Use CHAR(160) in place of the space if the source was a web page or PDF.
- If the cleaned text follows a fixed pattern such as dd/mm/yyyy, rebuild the date with DATE. For example, =DATE(RIGHT(B2,4),MID(B2,4,2),LEFT(B2,2)) reads positions in the text and builds a real date. Adjust the positions to match your pattern.
- Check the new column with ISNUMBER again. Once every row returns TRUE, copy the results and paste them as values over the original column, after keeping a backup copy of the file.
Fix a day and month mix-up
- Find a row where the first number is greater than 12. If that row only makes sense as day first, the source uses day-month order.
- Split the text into day, month and year parts. You can use your spreadsheet's split-text feature or LEFT, MID and RIGHT formulas.
- Rebuild each date with DATE(year, month, day), placing the parts in the correct order.
Avoid relying on DATEVALUE for this. It follows the regional settings of the computer running the file, so the same workbook can return different dates on two machines. Building the date from explicit parts gives the same result everywhere.
Fix formulas that show serial numbers
- Select the cells that contain the formula.
- Apply a date format from your spreadsheet's number format options.
- If the number still appears, check whether the cell was formatted as Text before you typed the formula. If it was, the formula is stored as text. Change the format to General or a date format, then enter the formula again.
Prevent it next time
Date problems often return when new data arrives in a different shape. A few habits keep the column clean.
- Set the column format to date before you type or paste values into it.
- Pick one date order for the whole team and write it in the header, such as "Due (DD/MM/YYYY)".
- Enter four-digit years so Excel does not have to guess the century.
- Add an ISNUMBER check column to every imported sheet, and filter for FALSE before building formulas on top of the data.
- Keep times out of date-only columns, or wrap dates in INT when you compare them.
- If your dates came from a PDF table, use the PDF editor in the OmniDoc desktop client (download.omnidoc.top) to export the table to Excel. Exported cells can still arrive as text, so run the ISNUMBER check before you rely on the results.
❓ FAQ
1. Why does my date formula work on one computer but not another?
The two computers probably use different regional settings for date order and separators. The same text, such as 03/04/2024, can be read as March 4 on one machine and April 3 on another. Rebuilding the date with DATE from separate parts avoids this.
2. Is DATEVALUE a good fix for text dates?
It can work for simple cases, but its result depends on the regional settings of the computer. Splitting the text and building the date with DATE is more predictable, especially when files move between people or countries.
3. Why does my formula fail in only some rows?
Those rows probably contain text, extra spaces, or a different date pattern. Use ISNUMBER to find the rows that fail, then compare their text to the rows that work.
4. Will a date column from a PDF table always export correctly?
The facts do not promise how every date will appear after export, so check the results. Run ISNUMBER on the exported column and compare a few values with the original PDF before you build formulas on them.
📝 Summary
When your Excel formulas for dates is not working, start by checking whether each cell holds a real date. Convert text dates by rebuilding them from their parts, and confirm the day and month order against the source. Once the column is clean, set the format and add an ISNUMBER check so the problem does not return.
