Excel date field to AS400

[David Abramowitz]

It's actually a Lillian date. Good luck. Dave

[Guest.Visitor]

Lee McDowell wrote: > I have an Excel file created by PeachTree with a date column. How do > I upload to the AS/400 without the date field being converted to that > julian-like number? Lee, Here's an example showing converting to and from a lillian date: http://archive.midrange.com/rpg400-l.../msg00494.html Bill

[Guest.Visitor]

Thanks for the replies, however here is what I have learned so far. I had never heard the term Lilian Date before, so I started my search at that point. IBM has an API (CEEDATE) that gives you the number of days after the Lilian Day, 10/14/1582, which aparently is the date the Gregorian calendar started. This looked promising, I could upload my Excel file and just convert the date. Unfortunately, the Lilian-like number used by Excel is actually tne number of days since 01/01/1900. Well, back to my research. If anyone has a suggestion, I would like to hear it.

[Guest.Visitor]

I have an Excel file created by PeachTree with a date column. How do I upload to the AS/400 without the date field being converted to that julian-like number?

[Guest.Visitor]

Lee McDowell wrote: > Thanks for the replies, however here is what I have learned so far. I > had never heard the term Lilian Date before, so I started my search > at that point. IBM has an API (CEEDATE) that gives you the number of > days after the Lilian Day, 10/14/1582, which aparently is the date > the Gregorian calendar started. This looked promising, I could upload > my Excel file and just convert the date. Unfortunately, the > Lilian-like number used by Excel is actually tne number of days since > 01/01/1900. Well, back to my research. If anyone has a suggestion, I > would like to hear it. Lee, It's just a bit more difficult. You just have to determine the difference in the number of days between 10/14/1582 and 01/01/1900 and add the result to the number coming in from Excel. Bill

[jacob6418]

I am using client access to create a spreadsheet from an AS400 file. Although a date field is defined as alpha in AS400 DDS specs, the field is populated as dd/mm/yy in the AS400 file. When the spreadsheet is created using client access, the field does not come across as text. I need the field to be text when uploading to another vendor's
___________________
Auto Parts
Car Parts

[D.Abramowitz]

The issue is not with the AS/400, but rather with Excel. If there are only numbers in a column, the excel assumes that the column should be numeric.

You will have to highlight the entire column, and right-click. Select "Format Cells" and then select "Text". This should solve your problem.

Dave