Problem with dates and formulas

Question

I have a formula that says, basically, "if the date is earlier than 1/1/2000, then display 'FAIL'".  I've used this formula successfully in one spreadsheet, then copied it into a new spreadsheet, but now the formula is not working.  There's no errors, but for a date of 1/1/1998, the result of FAIL is not being returned by the formula as it should. 

All of the cell references in the formula are correct, and there's some problem with interpreting the dates in the new spreadsheet.  The column of dates in the new spreadsheet was imported from another source.  If I type the date 1/1/1998 into the date cell, then the formula works. Thinking it's a formatting problem, I used both format painter and paste special - format to apply the format from the typed date into the rest of the dates, but this did not work.

The formatting of the original/imported dates and the hand-typed dates appears the same (X/X/XXXX), except the hand-typed dates are displaying as aligned right, and the imported dates are displaying as aligned left.  Both are set for General horizontal alignment. 

There is something different about the original/imported dates and the hand-typed dates that is causing my formula to either work or not work, but I can't figure out what else it could be.  I have thousands of data points and can't retype all of those dates in order for the formula to work.  Any ideas on what could be different between my dates?

Answer 1

The formatting of the original/imported dates and the hand-typed dates appears the same (X/X/XXXX), except the hand-typed dates are displaying as aligned right, and the imported dates are displaying as aligned left.  Both are set for General horizontal alignment. 

That's the key:  the imported dates are probably coming in as text, not numeric.  It does not matter what the numeric format is; you can still enter text.

Why are the imported dates coming in as text?  My guess:  they include spaces or non-breaking spaces.  The latter are common when you copy-and-paste from web pages.

If they only contain spaces, you can remedy the problem easily by using the Text To Columns feature.

If they contain non-breaking spaces, you need to use SUBSTITUTE to rectify the problem.  The following corrects both problems:

=--SUBSTITUTE(TRIM(A1),CHAR(160),"")

formatted as Date.  You can use copy-and-paste-special-value to replace the original imported dates with the SUBSTITUTE result.

Answer 2

Hi,

Try this

1. Select the column of dates
2. Go to Data > Text to columns > Finish

Does this resolve the problem.

Answer 3

Thank you!!!!!  Much frustration resolved.