1

I'm struggling to get Excel to determine if a value is greater than or less than.

In my table, I have column F which is a date. Column G is the following formula

LEFT(F2,6)

and this formula takes the first 6 characters from F, and sticks them onto the cell in column G, giving me year and month.

All good so far. The final part is confusing me. I want to treat the values in G as a number and compare them...

So, if the value in G is the same or greater than 202206 then it should show TRUE. If not it should be false. And from the screen shot, you can see, 202201 is not greater or equal to 202206 yet, it shows it is TRUE

Column G is formatted as General but even if I change it Number or Accounting the same issue persists

I also tried the following (didn't help)

= IF(G2 >= 202206)

Nor did

=IF(NUMBERVALUE(LEFT(G2,6))> 202206, "Y","N")

enter image description here

7
  • It could be that the column is formatted as TEXT.
    – patkim
    Aug 21, 2022 at 12:43
  • @patkim good shout, I forgot to mention I had tried making G Number of accounting - same issue Aug 21, 2022 at 12:44
  • what if you use VALUE function on that column, in your formula? It will convert valid numeric text to actual Number format. E.g. =VALUE(D3)>=202206
    – patkim
    Aug 21, 2022 at 12:45
  • 1
    @MyDaftQuestions To be honest, I’ve been getting more “flighty” behavior from Excel the last year or so. I spend a lot of time in excel, and often with fairly complicated worksheets, and I find myself more and more having to exit and re-open. I fear that the rich feature set we’ve been given in dynamic areas, etc. have come at the cost of some stability.
    – Max R
    Aug 21, 2022 at 15:26
  • 1
    I have always found your better off using the trim function. LEFT(trim(F2),6) Frequently, a space or other white space character sneaks in and messes you up.
    – cybernard
    Aug 24, 2022 at 17:48

1 Answer 1

0

Text functions always return text. To convert to a number, use a mathematical operation such as double negative.

=IF(--LEFT(G2,6)> 202206, "Y","N")

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .