Julian to regular date converter - Revision history

imported>Bob at 19:28, 12 March 2008

2008-03-12T19:28:40Z

imported>Bob at 19:50, 19 July 2007

2007-07-19T19:50:33Z

imported>Bob at 19:39, 19 July 2007

2007-07-19T19:39:53Z

imported>Bob at 19:38, 19 July 2007

2007-07-19T19:38:11Z

New page

Copy and paste the text in the box into the visual basic editor that comes with Excel.
Option Explicit
Public Sub convert_from_julian()
Dim row As Long
Dim startrow As Long
Dim theyear As String
Dim themonth As String
Dim theday As Integer
Dim thetime As String
Dim thedate As String
Dim leapyear As Integer
Dim julianday As Integer
Dim Worksheetz As Integer
Dim outcol As Integer
Dim yearcol As Integer
Dim daycol As Integer
Dim timecol As Integer
Dim rangeselect As String

'''''''''''''''''''''''''''''''
' variable initialization '
'''''''''''''''''''''''''''''''
startrow = 1
outcol = 1
yearcol = 3
daycol = 4
timecol = 5
Worksheetz = 1
'''''''''''''''''''''''''''''''

row = startrow
Do While Worksheets(Worksheetz).Cells(row, yearcol).Value <> ""
theyear = Worksheets(Worksheetz).Cells(row, yearcol).Value
julianday = Worksheets(Worksheetz).Cells(row, daycol).Value
thetime = Worksheets(Worksheetz).Cells(row, timecol).Value
Select Case Len(thetime)
Case Is = 1
thetime = "00:0" & Right(thetime, 1)
Case Is = 2
thetime = "00:" & Right(thetime, 2)
Case Is = 3
thetime = Left(thetime, 1) & ":" & Right(thetime, 2)
Case Is = 4
thetime = Left(thetime, 2) & ":" & Right(thetime, 2)
End Select

If theyear Mod 4 = 0 Then
leapyear = 1
Else:
leapyear = 0
End If

If julianday <= 31 Then
theday = julianday
themonth = "1"
ElseIf julianday <= 59 + leapyear Then
theday = julianday - 31
themonth = "2"
ElseIf julianday <= 90 + leapyear Then
theday = julianday - 59 - leapyear
themonth = "3"
ElseIf julianday <= 120 + leapyear Then
theday = julianday - 90 - leapyear
themonth = "4"
ElseIf julianday <= 151 + leapyear Then
theday = julianday - 120 - leapyear
themonth = "5"
ElseIf julianday <= 181 + leapyear Then
theday = julianday - 151 - leapyear
themonth = "6"
ElseIf julianday <= 212 + leapyear Then
theday = julianday - 181 - leapyear
themonth = "7"
ElseIf julianday <= 243 + leapyear Then
theday = julianday - 212 - leapyear
themonth = "8"
ElseIf julianday <= 273 + leapyear Then
theday = julianday - 243 - leapyear
themonth = "9"
ElseIf julianday <= 304 + leapyear Then
theday = julianday - 273 - leapyear
themonth = "10"
ElseIf julianday <= 334 + leapyear Then
theday = julianday - 304 - leapyear
themonth = "11"
ElseIf julianday <= 365 + leapyear Then
theday = julianday - 334 - leapyear
themonth = "12"
End If

' bring it all back together
themonth = themonth & "/"
theyear = "/" & theyear & " "
thedate = themonth & CStr(theday) & theyear & thetime
Worksheets(Worksheetz).Cells(row, outcol).Value = thedate
rangeselect = Chr(64 + outcol) & CStr(row)
' ascii A = 65
Range(rangeselect).Select
Selection.NumberFormat = "m/d/yyyy hh:mm;@"
row = row + 1
Loop

Range("A1").Select

End Sub

@@ Line 119: / Line 119: @@
    Loop
-   Range("A1").Select
+   Range("A" & CStr(startrow)).Select
    End Sub

@@ Line 1: / Line 1: @@
-Copy and paste the text in the box into the visual basic editor that comes with Excel.  To get access to personal.xls in the visual basic editor check out this article[[Link title]]
+Copy and paste the text in the box into the visual basic editor that comes with Excel.  To get access to personal.xls in the visual basic editor check out this article: [[Save_Macros_in_Excel]]
   Option Explicit
   Public Sub convert_from_julian()