How to convert date format from strange to normal

[NZFei]

Hello, I have created a qvw file to show qlikview server information. I have ServerName, Name, NoOfNamedCALs and so on. However for three fields LastUpdated,  LastUsed and ToBeDeleted, the value (should be date, aren't they?) are strange. They are 40E4067AD8153D10, 40E406245C71C71C, FE37E43C8800759C. I have tried to set the number formats for them to Date but it didn't work. Are they some kinds of date formats? How can I convert them to normal formats? Many thanks in advance!

[NZFei]

For example, the Lastupdated for a particular document is 40E4099EDEE8DD7D. I am pretty sure it is in this year 2012. However no matter how I tried to convert 40E4099EDEE8DD7D to Decimal number and calculate the date, the result is not in 2012.

[NZFei]

Maybe they are not UNIX timestamp in hexadecimal. I couldn't use any converter to convert them to dec and then add to qlikview sciprts

40E4067AD8153D10,

40E406245C71C71C,

FE37E43C8800759C.

For example through this website:

http://unixtimestamp.50x.eu/  

[rwunderlich]

Updating Steve's example:

=Date((num#('40E4099E', '(HEX)')/86400)+Date#('19700101', 'YYYYMMDD'), 'DD MMM YYYY')

-Rob

[NZFei]

Thank you very much Rob but the result is 2004, not 2012.

[rwunderlich]

I can't comment on your expectations of the source data. But I believe the conversion is "correct". Today's date in hex unix timestamp is 4FA85E73.

-Rob

[NZFei]

Thank you very much Rob!

[stevedark]

Sometimes application's timestamps are not actually representations of the time - just a unique string that has no value other than being a unique string.

It should be possible to find out if that is the case for this file.

- Steve

[rverschuren · Accepted solution]

You can convert these strange dates (actually the dates are 64 bit hexadecimals) by using a formula I posted in http://community.qlik.com/docs/DOC-4388.

René

[NZFei]

Thank you very much Rene.

[#11]

Hi, did you manage to convert the number 'FE37E43C8800759C' using the link René posted? I cannot make it work...