IF statement

  • Thread starter Thread starter PAL
  • Start date Start date
P

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.
 
=IF(AND(C2="",D2=""),"",IF(D2>C2,D2,C2))
In a few cases, C2 comes up as later when it shouldn't.
Click to expand...

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.
 
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.
 
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
 
Back
Top