Solved

Cobol date to standard excel date

Posted on 2014-02-06
6
429 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
6 Comments
 
LVL 50

Accepted Solution

by:
barry houdini earned 500 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 80

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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Office Picture Manager was included in Office 2003, 2007, and 2010, but not in Office 2013. Users had hopes that it would be in Office 2016/Office 365, but it is not. Fortunately, the same zero-cost technique that works to install it with …
This article will show you how to use shortcut menus in the Access run-time environment.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

839 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