Macro to calculate Human readable date.

[skalluri]

Can someone help me with a Macro to convert Decimal to Human readable date format please?

Eg: 1400158027078650000 should be converted to GMT: Thu, 15 May 2014 12:47:07 GMT


I tried using this cell reference formula but it doesnt seem to work (A1/86400)+25569+(-5/24)

Any other ideas please?

[Ron Coderre]

Can you explain what that number represents?
Can you post a few more examples (and their translated results)

[skalluri]

Hexadecimal code -> Decimal code -> Date for this from (http://www.epochconverter.com/)
136E5C4EA96F548C -> 1400158027078650000 -> Thu, 15 May 2014 12:47:07 GMT
1348ED3364706300 -> 1389621290028000000 -> Mon, 13 Jan 2014 13:54:50 GMT

[Ron Coderre]

Try this:
With
A1: a unix time.....1389621290028000000

This regular formula returns the Date/Time value and works if the reference is numeric OR text

Please Login or Register  to view this content.

to use a VBA User Defined Function, put this code in a general module:

Please Login or Register  to view this content.

Use the UDF this way in a worksheet:

Please Login or Register  to view this content.

Format B1 cell as custom time (m/d/yy h:mm:ss)

In that example, the formula returns: 41774.5327199074
which formats to: 5/15/2014 12:47:07

Is that something you can work with?

[skalluri]

Excellent...that works !!

Many Thanks for your help.