Change date format

75 pts.
Tags:
AS/400
i5
Microsoft Excel
I have a date field stored in the i5 as yymmdd that I would like to change to mmddyy when exported to Excel. If I just re-format the cell, Excel treats the number as a Julian date and mis-corrects the date. The other issue is that Excel drops the leading 0 so everything in this decade is represented by a single number. Any suggestions on how to handle this? thanks

Answer Wiki

Thanks. We'll let you know when a new response is added.

If your recipients don’t mind having to set the display formatting on the column to Date, the following code will create an Excel Date. (number of days since 12/31/1899)

d ExcelDate s 10i 0
c eval ExcelDate = %diff(%date(fld:*ymd):
c %date('1899-12-31'):*days) + 1

If the recipient doesn’t format the column to Date, they would see 39505 instead of 2/27/2008.

Note: The date will display in whatever format they choose (2/27/08, 27-Feb, etc)
Also, Excel Dates are only valid 1/1/1900 and greater

Kevin C. Ketzler

Discuss This Question: 4  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • DanBubis
    Thanks, I appreciate the answer but where would I place this code?
    75 pointsBadges:
    report
  • BigKat
    Ooops... assumed this was an RPGLE extraction program question. In the process that extracts your data, you need to setup the calculation to get the number of days between the current date and 12/31/1899. in SQL this would be:
    days(date(substr(digits(field2),3,2) || '/'
    || substr(digits(field2),5,2) || '/'
    || substr(digits(field2),1,2))) -
    days(date('1899-12-31')) + 1 as Excel_Date
    
    Hope this helps! Kevin
    8,330 pointsBadges:
    report
  • DanBubis
    I'm actually using Client Access to pull from a query. I think that you've given me enough of the syntax to make it work. Thanks.
    75 pointsBadges:
    report
  • TomLiotta
    You might also consider creating a SQL VIEW on your i5 that presents columns in the form that you want. By creating a VIEW, you can have many different spreadsheets pulling data from a single source. Tom
    125,585 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following