Forum Discussion

KaiViz's avatar
KaiViz
Copper Contributor
Apr 05, 2021
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 = 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!

  • 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)

4 Replies

    • KaiViz's avatar
      KaiViz
      Copper Contributor

      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.


      • Detlef_Lewin's avatar
        Detlef_Lewin
        Silver Contributor

        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)