Julian to regular date converter

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

This short program takes data from an array based Campbell Scientific data logger and adds a regular formatted date and time. There are several important parameters you may need to edit to get things working specifically these:

 startrow = 1
 outcol = 1
 yearcol = 3
 daycol = 4
 timecol = 5
 Worksheetz = 1  

startrow -- this is the starting row, the first line in the excel worksheet there is a date to be converted from Julian to regular.
outcol -- this is the output column, the column you want the resulting date to be put into. A = 1, B = 2 etc.
yearcol -- this is the column the year is in. If you don't have a year in your file you can do two things. One, you could add a column which contains the year. Otherwise you could go down into the code and rewrite it so that the year is being entered directly into the variable 'theyear' below.
daycol -- this is the column the julian day is in.
timecol -- this is the column the time is in. It doesn't matter if midnight is 24:00 or 0:00, the data should turn out correctly either way.
Worksheetz -- this is the excel worksheet number of the data you wish to calculate dates for. If you have a mix of charts and worksheets in your excel file worksheet #1 is treated as the leftmost worksheet on the bar along the bottom of the spreadsheet.
Well, with that all out of the way, here's the program. Get a hold of Bob Busey if you have any questions or problems running this.

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