Regular Date to Julian 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 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.
outcol -- this is the output column, the column you want the resulting date to be put into. A = 1, B = 2 etc.
datecol -- this is the column the Time & Date are in
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.
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