Formula help with using date.

[omairhe]

Hello all,

How can I write this formula to consider the first 7 characters as dates in cells D9 and D10 ?

=IF(LEFT(D9,7)=LEFT(D10,7),"A",IF(LEFT(D9,7)>LEFT(D10,7),"B",IF(LEFT(D9,7)<LEFT(D10,7),"C")))

D9 =

03-2021 (user.one).xlsb

D10 =

03-2021 (user.two).xlsb

answer = A


D9 =

04-2021 (user.one).xlsb

D10 =

03-2021 (user.two).xlsb

answer = B


D9 =

05-2020 (user.one).xlsb

D10 =

04-2021 (user.two).xlsb

answer = C

Thanks and will appreciate.

 

[6StringJazzer]

You are seeing if two text strings match. That works. Why does it matter if the represent dates or something else? Your formula is not trying to use them as dates.

 

[omairhe]

You are seeing if two text strings match. That works. Why does it matter if the represent dates or something else? Your formula is not trying to use them as dates.

There are three conditions to match. 1st is to check if both the dates are equal, then output A, but for the other two conditions the greater than and less than is required to output either B or C.
The values are in text becuase of the hyphen - in between the month and the year. Plus, in order to make this work it will have to recognize the years first and then the months.
For instance 03-2021 and 04-2020 will make 04-2020 greather than 03-2021. whereas it should be the other way around.
If 2021-03 and then 2020-04 will then give the correct answer, that is if the hypen was not included. And the text was considered as a number for greater than and less than to work. Or like my original post suggested, for the formula to condider it as a date.

 

[omairhe]

The requirement has slightly changed and carried forward to the given link. Thank you.

Max formula not working.

The max formula is not working with range D16 and D17. Value in D16 = 202103 Value in D17 = 202104 Formula in D16 = "=MID(D9,FIND("-",D9)+1,4)&LEFT(D9,2)" Formula in D17 = "=MID(D10,FIND("-",D10)+1,4)&LEFT(D10,2)" Thanks and will appreciate.

www.mrexcel.com