Improve company productivity with a Business Account.Sign Up

x
?
Solved

Cobol date to standard excel date

Posted on 2014-02-06
6
Medium Priority
?
473 Views
Last Modified: 2014-02-07
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
Comment
Question by:mobanker
  • 3
5 Comments
 
LVL 50

Accepted Solution

by:
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

by:mobanker
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

by:mobanker
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

by:David Johnson, CD, MVP
ID: 39840692
did you give barryhoudini the points for the answer?
0
 

Author Closing Comment

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

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

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.

Join & Write a Comment

Manually copying shapes and their assigned macros one by one to a new location can be tedious, but if you use the Excel utility workbook attached to this article, the process will be much quicker and easier.
What to do if a split doesn't fit? Or a bunch of invoice lines must be rounded while the sum must match a total? It takes a little, but - when done - it is extremely easy to implement.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

589 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question