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