IF statement

[PAL]

I have 2 columns w/ dates, copied over from a different spreadsheet. I am
trying to get the latter of the two dates to appear in a third column.
Sometimes there is no date, so the field is blank.

To find the latter date, I am using the formula:

=IF(AND(C2="",D2=""),"",IF(D2>C2,D2,C2))

In a few cases, C2 comes up as later when it shouldn't.

Ideas appreciated. Thanks.

 

[T. Valko]

=IF(AND(C2="",D2=""),"",IF(D2>C2,D2,C2))

In a few cases, C2 comes up as later when it shouldn't.

It could be that sometimes what's in C2 is a TEXT string that looks like a
date. If D2 is a true Excel date, which is really just a number formatted to
look like a date, and C2 is a TEXT string that looks like a date then the
result will be the value of C2.

In Excel a TEXT value will *always* evaluate to be greater than any numeric
number.

This formula will test and make sure there are 2 true Excel dates entered.
If there aren't then the result will be blank.

=IF(COUNT(C2,D2)<2,"",MAX(C2,D2))

If the formula returns a blank but it looks like you have 2 dates entered
that tells you one or both of your dates are TEXT strings and are not true
Excel dates.

 

[PAL]

You are right, but is there anyway to fix. It seems like the ones that are
wrong (and I am not sure why some are and some are not) need to be retyped to
fix.

 

[T. Valko]

Hard to say. One possibility is that some have leading/trailing whitespace
characters.

Sometimes this will work...

Select the range of cells in question. Cells that already contain true Excel
dates won't be affected by this.
Goto the menu Data>Text to Columns
Click Next twice
In step 3 of the wizard under Column data format, select Date and in the
drop down select the format that the dates are in.
Click Finish