imported>Bob at 19:28, 12 March 2008

2008-03-12T19:28:02Z

New page

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 date & time cells and converts it to Julian days. An additional option is a Julian Decimal day, which converts the time into a decimal quantity. There are several important parameters you may need to edit to get things working specifically these:
startrow = 13
outcol = 1
datecol = 2
Worksheetz = 1
decimalday = True

'''startrow''' -- this is the starting row, the first line in the excel worksheet there is a date to be converted from Julian to regular.<br>
'''outcol''' -- this is the output column, the column you want the resulting date to be put into. A = 1, B = 2 etc.<br>
'''datecol''' -- this is the column the Time & Date are in<br>
'''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.<br>
'''decimalday''' -- this boolean variable (True/False) tells the macro whether to output decimal days (as is done for some north slope sites) or an integer julian day.

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_To_Julian()
Dim row As Long
Dim startrow As Long
Dim theyear As Double
Dim themonth As Double
Dim theday As Double
Dim thehour As Double
Dim theminute As Double
Dim thedate As String
Dim tempstring1() As String
Dim tempstring2() As String

Dim leapyear As Integer
Dim julianday As Double

Dim Worksheetz As Integer
Dim outcol As Integer
Dim datecol As Integer

Dim decimalday As Boolean
''''''''''''''''''''''''''
' variable initialization '
''''''''''''''''''''''''''
startrow = 13
outcol = 1
datecol = 2
Worksheetz = 1
''''' note this decimal day.. If decimalday = true then hours & minutes will be tacked on
''''' if decimalday = false then the julian day will be an integer.
decimalday = True
''''''''''''''''''''''''''

row = startrow
Do While Worksheets(Worksheetz).Cells(row, datecol).Value <> ""
' Run through and pull the time/date from the spreadsheet
thedate = Worksheets(Worksheetz).Cells(row, datecol).Value

' separate out the date & time components individually
tempstring1 = Split(Format(thedate, "mm/dd/yyyy"), "/")
tempstring2 = Split(Format(thedate, "hh:mm"), ":")
themonth = CDbl(tempstring1(0))
theday = CDbl(tempstring1(1))
theyear = CDbl(tempstring1(2))
thehour = CDbl(tempstring2(0))
theminute = CDbl(tempstring2(1))

' use the year to check for the need for a leap day
If theyear Mod 4 = 0 Then
leapyear = 1
Else
leapyear = 0
End If

' Do the Julian Day calculations
If themonth = 1 Then ' JANUARY
julianday = theday
ElseIf themonth = 2 Then ' FEBRUARY
julianday = theday + 31
ElseIf themonth = 3 Then ' MARCH
julianday = theday + 31 + 28 + leapyear
ElseIf themonth = 4 Then ' APRIL
julianday = theday + 31 + 28 + leapyear + 31
ElseIf themonth = 5 Then ' MAY
julianday = theday + 31 + 28 + leapyear + 31 + 30
ElseIf themonth = 6 Then ' JUNE
julianday = theday + 31 + 28 + leapyear + 31 + 30 + 31
ElseIf themonth = 7 Then ' JULY
julianday = theday + 31 + 28 + leapyear + 31 + 30 + 31 + 30
ElseIf themonth = 8 Then ' AUGUST
julianday = theday + 31 + 28 + leapyear + 31 + 30 + 31 + 30 + 31
ElseIf themonth = 9 Then ' SEPTEMBER
julianday = theday + 31 + 28 + leapyear + 31 + 30 + 31 + 30 + 31 + 31
ElseIf themonth = 10 Then ' OCTOBER
julianday = theday + 31 + 28 + leapyear + 31 + 30 + 31 + 30 + 31 + 31 + 30
ElseIf themonth = 11 Then ' NOVEMBER
julianday = theday + 31 + 28 + leapyear + 31 + 30 + 31 + 30 + 31 + 31 + 30 + 31
ElseIf themonth = 12 Then ' DECEMBER
julianday = theday + 31 + 28 + leapyear + 31 + 30 + 31 + 30 + 31 + 31 + 30 + 31 + 30
End If

'' If we need decimal days then add the decimal part here.
If decimalday = True Then
julianday = julianday + thehour / 24 + theminute / 24 / 60
End If

'' ready to output back to the original spreadsheet.
Worksheets(Worksheetz).Cells(row, outcol).Value = julianday

'' Go to the next row.
row = row + 1
Loop
Range("A" & CStr(startrow)).Select

End Sub

Regular Date to Julian Converter - Revision history

imported>Bob at 19:28, 12 March 2008