Strange date format

[mcnorth]

I'm having a miserable time trying to figure out a strange date format that is used in a stock trading program. Here are some of the values.

12-11-2001 b0c07694
01-02-2002 600c7994
02-01-2002 90127994

If anyone can shed some light on this I would gratly appreciate it.

 

[vb5prgrmr]

mcnorth, have you read FAQ222-2244 yet?

It looks like it could be a hex value. Check to see if there is a letter higher than F (G - Z). I would then bet if it is a hex value it is the number of seconds from a specific time, whether that is from GMT or EST midnight or the opening bell for the market for the day I couldn't tell you.

Good Luck

 

[mcnorth]

It's definitely hex values as they come from a binary file. I'm just baffled as to how to make sense of them. I considered the "seconds from a specific date" logic, thinking that then I could use "DateSerial" on them, but the difference from one day to the next is only a value of 16. Not enough for seconds, too much for days and just right to be wrong.

Re: mcnorth, have you read FAQ222-2244 yet?

No I hadn't read that. Should I have not posted to this forum? If so, my apologies. I just joined. As for putting in some effort to reolving this before posting, I've been at it for days.

Thanks vb5prgrmr! I appreciate your response.

 

[vb5prgrmr]

>Should I have not posted to this forum?

If you are using VB (not VBA) then this is the forum for you.

>I considered the "seconds from a specific date" logic,

You have the date! It would be seconds from a time on that date!

Good Luck

 

[mcnorth]

But it can't be because there are only 16 value units from one day to the next and there are 86,400 seconds in each day.

 

[vb5prgrmr]

and
[tt]
?hex(86400)
15180
[/tt]
So its slowly dawning upon me what you mean.

I also tried it based upon an 8, 8.5, and 9 hour day. The 8 hour day looked promising but its values were a little to low so I incresed by 1/2 hour increments and the 8.5 hour day was almost there but when I got to a 9 hour day it blew that theory out of the water. So your guess is as good as mine at the moment (yours is probably better).

Good Luck

 

[mcnorth]

Ugly, isn't it? Right now I'm chasing floating point numbers. There's some reason to believe that the values I posted may be the old Microsoft binary format that is then converted to IEEE. I'm using a dll for conversion that is available here.

http://www.mdxi.com/products.html

(in case it has any value to someone that happens along) Floating point numbers are a whole other headache.

Thanks again for taking a look at this.

 

[mcnorth]

PROBLEM SOLVED!

This is the old Microsoft binary floating point format in which the first 8 bits (most significant) are the exponent, the next one is the sign and the last 23 (least significant) are the mantissa. The result gives a 7 digit number in which the left 3 digits are the number of years since 1900 (good call vb5prgrmr) the next 2 are the month and the last 2 are the day.