Apr 05 2021 05:58 PM
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!
Apr 05 2021 07:21 PM
=2018+(HEX2DEC(LEFT(A1))<5)*3
=HEX2DEC(RIGHT(A1))
Apr 05 2021 08:38 PM - edited Apr 05 2021 08:41 PM
@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.
Apr 05 2021 10:44 PM
SolutionSo 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)
Apr 06 2021 06:59 PM
Apr 05 2021 10:44 PM
SolutionSo 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)