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?
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
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.