IExcel not comparing dates properly

I have a table with two dates in the following format:

C1 Sep 21, 2017 10:22 AM
C2 Sep 28, 2017 10:09 AM

If I run the following if statement against them...

=IF(M4<L4, "Fail", "Pass")

...then the formula returns Pass. However if the two dates are in different months the formula fails.

For example,

C1 [Sep 26, 2017 03:14 PM]
C2 [Oct 2, 2017 03:39 PM]
returns Fail.

The value of C1 is lower than C2 yet the formula still fails.

It doesn't make a difference to the return value if the cell is formatted as text or date. and the =DateValue() function will not work, it returns #VALUE

Any Ideas?

SalemTheCat

Posted 2018-07-31T11:12:06.260

Reputation: 11

essentially i am looking to compare the values of each column. c1 is the time something was fixed and c2 is the time it is required to be fixed by. If the time it was fixed is after the due time then the formula needs to respond as failed otherwise its a pass – SalemTheCat – 2018-07-31T13:23:48.570

I think your last point that DateValue doesn't work is the problem, if Excel can't parse the text into what it recognizes as date, then it will be likely doing text comparisons, and hence "O" is < "S". Try formatting the data so that DateValue parses. – cjb110 – 2018-07-31T13:47:01.833

@SalemTheCat so I assume right that C1 stands for Column 1 and C2 stands for column 2? So the cells you are referring to are actually M4 and L4, right? – Albin – 2018-07-31T16:13:22.883

@SalemTheCat you should not only write you're specifications into the comment but also edit you're question so it will be easier to read. I did it for you this time. – Albin – 2018-07-31T16:21:01.140

@SalemTheCat I updated my answer, it should work now, at least it works in my excel version. – Albin – 2018-08-01T09:42:12.327

Answers

In you're formula you are not comparing C1 and C2 but M4 and L4. If you want to compare C1 and C2 directly you should use those values in you're formula as well.

This formula, for example

=IF(C1<C2, "Fail", "Pass")

will show "Fail" if the date/time in cell C1 is smaller then the Date in cell C2. If the date in C1 is larger or equal it will show "Pass". The cells C1 and C2 should be formatted as date, not as text. In both cells it has to be a date or user defined format (which uses the time and date variables excel offers, like YYYY, MMM, DD etc.). If you use text-format the "if-clause" won't compare time/date but strings, which gives you you're "funny" results!

The function DateValue() only works on cells with text-format and only within a certain scope, it does not work with you're notation. It works with 31.07.2018 20:36 but does not work with Jul 31, 2018 08:36 PM.

Albin

Posted 2018-07-31T11:12:06.260

Reputation: 3 983

If statements works in the following way:

=IF( Logical Test, Value if test is TRUE, value if test is FALSE)

So you may want to use:

=IF(C2<C1,"Pass",Fail")

I did not 100% understand your question, so you may need to swap < for >

Logical operators you can use:

> , Greater Than
< Less Than
= Equals
<= Less Than or equal to
>= greater than or equal to
<> does not equal

PeterH

Posted 2018-07-31T11:12:06.260

Reputation: 5 346