Julian Date to mm/dd/yy in excel

Question

I am trying to convert a julian date (yy/ddd) to a regualr mm/dd/yy data in excel vba. I have an idea of using a select case statement that calls a function for each month. But that is alot of code and I am lazy. I am wondering if there is a better way?

Answer 1

According to this website:

=DATE(IF(0+(LEFT(A1,2))<30,2000,1900)+LEFT(A1,2),1,RIGHT(A1,3))

It it works as as long as you have a five-digit number in A1. For example, it turns 95032 into 2/1/1995. You'll need to adjust the formula if your data actually has a slash in it (95/032).

The same site has a vba section, as well. It says that the following will work:

Function JDateToDate(JDate As String) As Long
Dim TheYear As Integer
Dim TheDay As Integer
Dim TheDate As Long

TheYear = CInt(Left(JDate, 2))
If TheYear < 30 Then
    TheYear = TheYear + 2000
Else
    TheYear = TheYear + 1900
End If

TheDay = CInt(Right(JDate, 3))
TheDate = DateSerial(TheYear, 1, TheDay)
JDateToDate = TheDate

End Function

I didn't test the vba code, but since it is essentially a code version of the above formula, it should do the job.

Edit: As tbischel points out, this is not actually a Julian Date. It is an Ordinal Date.