<-- Return to Excel Statistics Page

Excel VBA to Calculate a Julian Date

Keith Greiner

August 9, 2020

This essay shows how to calculate the Julian date for astronomical calculations. There are two Julian date systems in practice, today. One is used by IT systems to record the year number, and day of the year in a concatenated string in the yyyyddd, or yyddd format. The astronomical Julian date is the number of days since the date and time of noon, on January 1, -4712 BCE. On the day this is written, August 22, 2017 the Julian date, at midnight was 2,457,987.5. Accurate calculation of the Julian date is problematic. The VBA program shown below was written to implement calculations described by a University of Texas document at: https://quasar.as.utexas.edu/BillInfo/JulianDatesG.html . If you use the VBA, be sure to verify it with other sources to make sure that the procedure continues work for your situations. I don’t guarantee either the procedure provided by the University of Texas site, or the program. If it doesn’t work for you, then use this as an opportunity to work on your debugging skills. Remember that code that has been copied and pasted from a web page to your Excel VBA file, may contain hidden characters that are essential for the html, but wreak havoc on a compiler’s ability to run your program. You may need to simply re-type the code to get it to work correctly. As you start, know that the function shown below worked in my Excel workbook.

The code is below.

Public Function Date_to_Julian_Calendar(ByVal vDate As Date)

'VBA for Julian Date based on University of Texas document

' This VBA code comes with no warranty whatsoever 

'Source:  https://quasar.as.utexas.edu/BillInfo/JulianDatesG.html

Dim Y As Long

Dim M As Long

Dim D As Long

Dim a As Double

Dim b As Double

Dim C As Double

Dim E As Double

Dim F As Double

Dim JD As Double

Y = Year(vDate)

M = Month(vDate)

D = Day(vDate)

'GoTo Skipout1  ' use this for testing

If M = 1 Or M = 2 Then

  Y = Y - 1

  M = M + 12

End If

Skipout1:

a = (Y / 100)

a = Int(a)

b = a / 4

b = Int(b)

C = 2 - a + b

C = Int(C)

E = 365.25 * (Y + 4716)

E = Int(E)

F = 30.6001 * (M + 1)

F = Int(F)

JD = C + D + E + F - 1524.5

Date_to_Julian_Calendar = JD

End Function

<-- Return to Excel Statistics Page