Strange Date Format

[Seth Schrock]

I'm working with some data that I have imported from another system, and it has a very strange date format. It seems like everything after 1/1/2000 has the year formated with a letter. For example, 11/04/A0 or 08/04/B6. I have never seen this format before and I'm not sure how to translate this into a date that I can compare. Has anyone else seen it or know what it is?

[Rabbit]

I have never heard of that. The first thing that jumps to mind is a hexadecimal decade system where A0 would be 2000 because 00 would be 1900. And B6 would be 2016.

[Rabbit]

Mayhaps this?

http://www.3kranger.com/LabNotes/LN0010.htm

[Seth Schrock]

I think that your link is exactly the date format that I have, but the patch that it talks about doesn't seem to be available anymore. Also, I don't believe that it is an Image SQL database.

I can't find anything about a hexadecimal decade system, so I'm not sure how to convert it to a year. Simple hexadecimal to decimal doesn't make sense either.

[Rabbit]

Well, if I understand correctly, it solves the Y2K problem up to the year 2059. Where A = 200x, B = 201x, ..., F = 205x

[Seth Schrock]

Well, it isn't hexadecimal. One of the dates is 10/27/M4, which is 10/27/2124. So instead, I use the character code to determine how much to add to the 1900 of 0. But since the years below 2000 just use numbers, I have to test the beginning character of the year to see how to format the date. So here is the formula that I ended up using

=DATE(
    IF(CODE(RIGHT(A1, 2))<65
        ,1900+A1
        ,1900+10*(CODE(UPPER(RIGHT(A1,2)))-55)+RIGHT(A1,1))
    , LEFT(A1, 2)
    , MID(A1, 4, 2))

Note: my dates have two digit month and day value always.

[Rabbit]

That was my next guess if it wasn't hex. This is a weird solution to Y2K

[Seth Schrock]

I would agree with that!