Excel formula to convert single number to year with less then/greater then

[KaiViz]

Hi all,

Not sure what to name this topic, so sorry in advance for whatever convoluted name I came up with.

Basically, I need to take a 2 digit (hexadecimal) year/month combo, i.e.:

81 = January of 2018
or, 1A = October of 2021.

And convert that into the year.  So take 8C and have cell 1 say 2018, and cell 2 say 12.

I know =LEFT(B3,1) will get me the digit i want, and I know =IF(B15*1<5,"202" & B15,"201"&B15) would work (Change the 5 to whatever I need the cutoff year to be)  But how do I combine them?

Same question for the month, I know =RIGHT(B3,1) would get me the month, but then how do i convert the hexadecimal months (A,B,C) into the decimal months?  would the HEX2DEC function work correctly in combination with the right command?  

I'd prefer not to have to do this over multiple cells.  I want it to be 3 cells.

Cell 1: Manually/automatically insert the Year/Month combo.
Cell 2: Formula for Year
Cell 3: Formula for Month.

Thanks in advance!

[#2]

@KaiViz 

=2018+(HEX2DEC(LEFT(A1))<5)*3
=HEX2DEC(RIGHT(A1))

[#3]

@Detlef Lewin 

The month code works great, thanks!  But the other one just gives me 2018 or 2021.  It actually needs to do the year.  Which I see I didn't explain fully.

So 1=2021, 2=2022, 3=2023, 4=2024, 5=probably 2025, but then 6 = 2016, 7=2017.

So this if statement works: =IF(B1*1<5,"202" & B1,"201"&B1)

But doesn't incorporate the LEFT tag, which is where I run into issues trying to get it to work.

[#4 · Accepted solution]

@KaiViz 

So it's not a hexadecimal value but a combination of decimal and hexadecimal values.

Who invents such c***?

=IF((--LEFT(A1))<5,"202","201")&LEFT(A1)

[#5]

Yeah it's a thing. The month is hexadecimal, the year is decimal, so it's two separate things in the same line but I need to separate because...that's how life works.

In any case, that's totally awesome and works super well! Thanks very much!