Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Cobol date to standard excel date

Posted on 2014-02-06
6
Medium Priority
?
448 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
6 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 83

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

New benefit for Premium Members - Upgrade now!

Ready to get started with anonymous questions today? It's easy! Learn more.

Question has a verified solution.

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

My attempt to use PowerShell and other great resources found online to simplify the deployment of Office 365 ProPlus client components to any workstation that needs it, regardless of existing Office components that may be needing attention.
This article describes a serious pitfall that can happen when deleting shapes using VBA.
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 create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

704 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