Expiring Todayâ€”Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

# date conversion

Posted on 2000-03-11
Medium Priority
363 Views
I need a way to convert a Julian date to a standard date. I am connected to an AS400 table that holds Julian dates and would like to convert those dates on a form to a standard date that makes sense to my users.
0
Question by:n7kon
[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

LVL 7

Expert Comment

ID: 2607826
This should work just fine:

Function JulianToNormal(strJulian As String) As Date
Dim strYear As String
Dim intDays As Integer
Dim datResult As Date

'Extract the year
strYear = Left\$(strJulian, 2)

'Extract the number of days
intDays = CInt(Right(strJulian, 3))

'Start with January 1 of the year
datResult = CDate("1/1/" & strYear)
'Add number of days - 1
datResult = DateAdd("d", intDays - 1, datResult)
JulianToNormal = datResult
End Function

Note: year 00-29 is 2000-2029, 30-99=1930-1999
0

LVL 10

Expert Comment

ID: 2607840
Hello n7kon,

Here are two functions you could use. The first one converts calendar date to Julian date, second Julian date to calendar date.

'********************************************************
' Public Function DateToJulian( dtmDate )
' Converts the passed calendar date to a Julian date
' number.
'********************************************************
Public Function DateToJulian(dtmDate As Date) As Long
DateToJulian = (dtmDate - #1/1/100#) + 1757585
End Function

'********************************************************
' Public Function JulianToDate( lngJulian )
' Converts the passed julian date number to a calendar
' date
'********************************************************
Public Function JulianToDate(lngJulian As Long) As Date
JulianToDate = lngJulian - 1757585 + #1/1/100#
End Function

Regards,
0

LVL 10

Expert Comment

ID: 2607857
seems that the suggestions are posted before the question was here...

Question time: Saturday, March 11 2000 - 05:54PM EET

BrianWren's comment: Saturday, March 11 2000 - 05:17PM EET

My comment: Saturday, March 11 2000 - 05:27PM EET

?-)
0

LVL 10

Expert Comment

ID: 2607869
oops.. forgot to post functions to convert Long to Date (and vice versa). Here they are:

'********************************************************
' Public Function DateToNumericDayYear(dtmDate As Date)
' Converts the passed date to the numeric day of the year
' (1-366)
'********************************************************
Public Function DateToNumericDayYear(dtmDate As Date) As Integer
DateToNumericDayYear = dtmDate - _
DateSerial(DatePart("yyyy", dtmDate), 1, 1) + 1
End Function

'********************************************************
' Public Function NumericDayYearToDate( intNum )
' Converts the numeric day of the year (1-366) to a
' calendar date
' in the current year
'********************************************************
Public Function NumericDayYearToDate(intNum)
NumericDayYearToDate = DateSerial(DatePart("yyyy", Date), 1, 1) + intNum - 1
End Function

These functions are not made be me, the original source code is written by Sal Ricciardi.

Regards,
0

LVL 10

Expert Comment

ID: 2607879
Believer, not BrianWren.. maybe I need new glasses. Sorry confusion.
0

LVL 9

Expert Comment

ID: 2608523
I was reading the thread, and wondered how I managed to not only answer before the question was asked, but additionally I appeared to have answered before I answered!!!  :-/ hmmm...

The server for EE always gives a post time that is an hour late.  When we 'leap ahead,' I suspect that it will be right finally, (for about half a year...)

Brian
0

Accepted Solution

viveka031100 earned 400 total points
ID: 2608569
When using Access to external databases I normally recommend Pass-thru queries.  The pass- through queries allow platform sql statements and I am quite sure AS400 databases have native datew convert functions.

Using pass-thru has significant performance improvement.  The flip side is Access does not handle pass-thru  queries in sub-reports.  It somehow finds out the pass-through query - does not matter how deep you bury it ( I tried upto 3 levels.)

You may like to look up MSN site.  They have a whole bunch of date conversion routines.

The functions posted above will work with the following exceptions:
a)  does not recognize a bad Julian date ( 99500).
b)  does not recognize the 14 days skipped in October 4 (1782???)
c)  Somehow access does not return correct date offset for years beyond 2140 (erratic results)
0

LVL 10

Expert Comment

ID: 2609505
Welcome To Experts Exchange viveka!

It is customary to not Answer a question unless you are 120% sure it will completely address the question. When the question is answered, it gets moved into the Locked Section, thus reducing the potential responses.

If you post your response as a comment, then the question gets more visibility in the Unanswered Section. If the asker feels that your comment is an appropriate response for their question they have the ability of accepting it as an answer and awarding you the points.

Also we have a customary here in Access Topic that experts post all their suggestions as comments.

----

I think your suggestion and information provided in your posting is very valuable to both the customer and experts, thank you. Would you please change your answer to comment.

Best regards,
0

Author Comment

ID: 2622168
Thanks for all the help. vivekas answer works and so dose paasky. If I knew how I would increas and split the points. As a new user here I am not sure if that can be done. But Thanks to Both.
0

LVL 10

Expert Comment

ID: 2622354
Hello n7kon,

You can post a request to EE Community support if you want to split the points ( http://www.experts-exchange.com/Customer_Service/Experts_Exchange/ )

Sometimes customers post extra question to give points for expert who has helped him/her, subject: For <expert> (Q.99999999)

regards,
0

## Featured Post

Question has a verified solution.

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

Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retrâ€¦
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the â€śRepeat Sectionâ€ť property of the dummyâ€¦
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â€¦
###### Suggested Courses
Course of the Month11 days, 8 hours left to enroll

#### 719 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.