x
Solved

# Cobol date to standard excel date

Posted on 2014-02-06
Medium Priority
473 Views
I have a list of dates outputted from a COBOL program that are in this format:

Here is a date from the file = 0206139

That is 7 bytes, the first byte is zero.  The next byte is 2, which means century ‘20’ .  The next 2 bytes are ‘06’, that is the year.  The next 3 bytes is the Julian day. ‘139’  is May 19th.  So that date is 5/19/2006.

Can anyone help me make an excel formula to convert those dates for me? Any month, year, date combination would work for me.

Thank you
0
Question by:mobanker
• 3

LVL 50

Accepted Solution

barry houdini earned 2000 total points
ID: 39839893
If you have 0206139 in A1 try this formula in B1 to get your date

=DATE(LEFT(A1+0,3)-100,1,RIGHT(A1,3))

That certainly works for 21st Century - do you have earlier dates - what do 1999 dates look like?

regards, barry
0

Author Comment

ID: 39840444
Barry,

Sorry for the slow response.  My data happens to be only 2000 and later dates, so that is great.  I tested the formula out, I had to remove the leading zero to make it work, but it got the job done and your date matched the example.

Thank you for your help - Great job!
0

Author Comment

ID: 39840691
I've requested that this question be closed as follows:

Accepted answer: 0 points for mobanker's comment #a39840444

for the following reason:

His answer matched my specifications quite well.
0

LVL 85

Expert Comment

ID: 39840692
did you give barryhoudini the points for the answer?
0

Author Closing Comment

ID: 39841999
His answer was very close to my specifications and worked.
0

## Featured Post

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.