Solved

Cobol date to standard excel date

Posted on 2014-02-06
6
439 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 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 82

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: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

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

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Outlook for dependable use in a very small business   This article is about using the Outlook application (part of Microsoft Office) in a very small business, or for homeowners where dependability and reliability are critical requirements. This …
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
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…

623 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