excel - 将 Qlikview 十六进制日期转换为普通日期

Question

我从 Qlikview 导出了一个 XML 文件，日期采用 16 个字母/数字形式(即 40E5A40D641FDB97)。我尝试了多种方法将其转换为浮点小数，然后转换为日期，但所有方法都失败了(包括 Excel HEX2DEC)。
以前有人处理过这个问题吗？非常感谢任何帮助!

Answer 1

这是一个 Power Query 例程，它将将该十六进制数转换为其等效日期:
我使用查找表生成十六进制数的二进制等效项并将结果连接起来。
该算法在编码中应该是清晰的，并且遵循 IEEE-754 中规定的规则。
对于您在问题中提到的日期，它提供了相同的结果。
请注意，当您从 Qlikview 描述日期表示时，此例程假定编码的有效值。它不是一个通用的例程。

let 

//don't really need the Decimal column
hexConvTable = Table.FromRecords({
    [Hex="0", Dec=0,  Bin = "0000"],
    [Hex="1", Dec=1,  Bin = "0001"],
    [Hex="2", Dec=2,  Bin = "0010"],
    [Hex="3", Dec=3,  Bin = "0011"],
    [Hex="4", Dec=4,  Bin = "0100"],
    [Hex="5", Dec=5,  Bin = "0101"],
    [Hex="6", Dec=6,  Bin = "0110"],
    [Hex="7", Dec=7,  Bin = "0111"],
    [Hex="8", Dec=8,  Bin = "1000"],
    [Hex="9", Dec=9,  Bin = "1001"],
    [Hex="A", Dec=10, Bin = "1010"],
    [Hex="B", Dec=11, Bin = "1011"],
    [Hex="C", Dec=12, Bin = "1100"],
    [Hex="D", Dec=13, Bin = "1101"],
    [Hex="E", Dec=14, Bin = "1110"],
    [Hex="F", Dec=15, Bin = "1111"]},
            type table[Hex = Text.Type, Dec = Int64.Type, Bin = Text.Type]),

    hexUp = Text.Upper(hexNum),
    hexSplit = Table.FromList(Text.ToList(hexUp),Splitter.SplitByNothing(),{"hexNum"}),

//To sort back to original order
    addIndex = Table.AddIndexColumn(hexSplit,"Index",0,1,Int64.Type),

//combine with conversion table
    binConv =   Table.Sort(
                    Table.Join(
                        addIndex,"hexNum",hexConvTable,"Hex",JoinKind.LeftOuter),
                    {"Index", Order.Ascending}),

//equivalent binary
    binText = Text.Combine(binConv[Bin]),
    sign = Text.Start(binText,1),

//change exponent binary parts to numbers
    expBin = List.Transform(Text.ToList(Text.Middle(binText,1,11)),Number.FromText),

//exponent bias will vary depending on the precision being used
expBias = 1023, //Number.Power(2,10-List.PositionOf(expBin,1))-1,

    expPwr= List.Reverse({0..10}),
    exp = List.Accumulate({0..10},0,(state, current) =>
            state + (expBin){current} * Number.Power(2,expPwr{current})) - expBias,

    mantBin = List.Transform(Text.ToList(Text.Middle(binText,11,52)),Number.FromText),
    mantPwr = {0..51},
    mant = List.Accumulate({0..51},0,(state, current) =>
            state + (mantBin){current} / Number.Power(2,mantPwr{current})) +1,
    dt = mant * Number.Power(2,exp)
in 
   DateTime.From(dt)