If statements using less than or equal with dates

[Tigers00]

So F4 and O2 below are dates in the format mm/dd/yy and I'm trying to get the
function to pull the value in column M if the statement is true. For some
reason all of the values are coming up "" blank when they shouldn't be. Can
you please help?

=IF($F4<=O$2,$M4/1000,"")

 

[smartin]

So F4 and O2 below are dates in the format mm/dd/yy and I'm trying to get the
function to pull the value in column M if the statement is true. For some
reason all of the values are coming up "" blank when they shouldn't be. Can
you please help?

=IF($F4<=O$2,$M4/1000,"")

Works for me. I'm guessing one or more of your "dates" are actually
text. Here's one way to check:

Press Ctrl+~

If your "dates" still look like dates, they are actually text.

If they look like 5-digit numbers, they are actually dates.

Assuming you discovered your "dates" are in fact text, you can probably
convert them with this time-tested trick:

First make sure the date cells are formatted as dates, if you haven't
already. This will not change the way the data is actually stored, until
you do the next step.

Next select a blank cell (i.e., one with /nothing/ in it), copy, select
an errant "date", edit | paste special and choose options "values" and
"add".

Oh yeah, press Ctrl+~ again at any time to restore the values view on
the worksheet.

 

[T. Valko]

The date in cell F4 is probably not a true Excel date. It's probably a TEXT
string that looks like a date. In Excel daates are really just numbers
formatted to look like dates. You can test a date to see if it is in fact a
true Excel date.

=ISNUMBER(F4)

If F4 is a true Excel date that formula will return TRUE.

=COUNT(F4,O2)

If both cells contain true Excel dates that formula will return 2.

One way to fix it is to change the cell format to GENERAL then re-enter the
date. If you have a lot of cells to fix sometimes this will work:

Select the range of cells in question
Goto the menu Data>Text to Columns
Just click Finish