Forum Discussion
Solved
Excel formula to convert single number to year with less then/greater then
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 = Janu...
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)
=2018+(HEX2DEC(LEFT(A1))<5)*3
=HEX2DEC(RIGHT(A1))
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.
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)- 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!