Converting 64 bit hexadecimal (PGO dates) to decimal

    Hi,

     

    Dates (like the LastUsed date) in the CalData.pgo.xml file are saved as 64 bit hexadecimal numbers.

    Those numbers can be converted to decimals by using the double precision floating point format (IEEE-754 standard).

    And if you have a decimal number, you can format the number as a date.

     

    I wrote a very powerful formula for this type of conversion.

    You can use this formula anywhere in your QlikView document, because it is just some code in a variable.

    Just add a new variable with the name eConvertHexToDec and paste in the formula from the attached text file.

     

    After adding the formula to your document, you can use it like any other variable.

    One thing, you must use dollar sign expansion.

    So, this is the right way: $(eConvertHexToDec(LastUsed))

     

    If you want to test it with the Pi number in a textbox, you must enter this in the text field:

    =$(eConvertHexToDec('400921FB54442D18'))

    And the result should be 3.1415926535898.

     

    For an example look at the attached QlikView document.

     

    One more thing, the hexadecimal number must always be 16 characters long.

     

    Have fun with it.

     

    Kind regards,

    René Verschuren

    The Netherlands