XL: Visual Basic Procedures for Julian Date Calculations

• Microsoft Excel for Windows, version 5.0
• Microsoft Excel for Windows 95, version 7.0
• Microsoft Excel 97 for Windows

SUMMARY

This article describes how to convert between the serial date, normal date, and Julian date formats using Visual Basic, Applications Edition procedures.

MORE INFORMATION

Microsoft Excel normally stores date values as serial numbers. For example, January 1, 1994, is stored as the number 34335. The serial number 1, represents January 1, 1900. All of these calculations are based on the 1900 date system.

Julian dates are used to show the number of days that have passed and/or that are left in a given year. The format for a Julian date is the following:

   yyddd

   Serial date          Normal date          Julian date
   -----------          -----------          -----------

   34335                Jan-01-1994          94001
   34699                Dec-31-1994          94365

Microsoft provides examples of Visual Basic procedures for illustration only, without warranty either expressed or implied, including but not limited to the implied warranties of merchantability and/or fitness for a particular purpose. This Visual Basic procedure is provided 'as is' and Microsoft does not guarantee that it can be used in all situations. Microsoft does not support modifications of this procedure to suit customer requirements for a particular purpose. Note that a line that is preceded by an apostrophe introduces a comment in the code--comments are provided to explain what the code is doing at a particular point in the procedure. Note also that an underscore character (_) indicates that code continues from one line to the next. You can type lines that contain this character as one logical line or you can divide the lines of code and include the line- continuation character. For more information about Visual Basic for Applications programming style, see the "Programming Style in This Manual" section in the "Document Conventions" section of the "Visual Basic User's Guide."

'This procedure takes a Julian date (yyddd) entered in an Input Box
'and converts it to the appropriate serial date.
Sub JulianToSerial()
  'Holds the value of the string entered in the Input Box.
  Dim JulianDate As Long
  'The converted serial date value.
  Dim SerialDate As Date

  'Prompt for input and assign the value entered to the
  'JulianDate variable.
  JulianDate = Val(InputBox("Enter the Julian date (yyddd):"))

  'Convert the Julian date to a serial date.
  SerialDate = DateSerial(1900 + Int(JulianDate / 1000), 1, _
     JulianDate Mod 1000)

  'Display the new date in the normal date format.
   MsgBox "The equivalent date is " & SerialDate
End Sub

'This procedure takes a serial date number entered in an Input Box
'and converts it to the appropriate Julian date and returns it as a
'string.
Sub SerialToJulian()
    Dim SerialDate As Date      'The serial date.
    Dim SerialYear As String    'The year of the serial date.
    Dim JulianDay As String
    Dim JulianDate As String    'The converted Julian date value

    'Prompt for input and assign the value entered to the
    'SerialDate variable.
    SerialDate = Val(InputBox("Enter the serial date to convert:"))

    'Assign SerialYear the year number
    SerialYear = Format(SerialDate, "yy")

    'Find the day number for SerialDate
    JulianDay = Format(Str(SerialDate - DateValue("1/1/" & _
    Str(SerialYear)) + 1), "000")
    JulianDate = SerialYear & JulianDay

    'Display the new date in the normal date format.
    MsgBox "The equivalent Julian date is " & JulianDate
End Sub

'This procedure takes a normal date format (that is, 1/1/94) entered in
'an Input Box and converts it to the appropriate Julian date.
Sub NormalToJulian()
    Dim CRLF As String          'Carriage return/line feed.
    Dim NormalDate As Date      'The serial date.
    Dim DateYear As String      'The year of the serial date.
    Dim JulianDay As String
    Dim JulianDate As String    'The converted Julian date value

    'Assign CRLF the value of a carriage return and line feed.
    CRLF = Chr$(13)

    'Prompt for input and assign the value entered to the
    'NormalDate variable.
    NormalDate = DateValue(InputBox("Enter the date to convert:" & _
    CRLF & "Examples: " & CRLF & "1/1/94" & CRLF & "12/31/1994" _
    & CRLF & "Jan-05-94"))

    'Assign DateYear the year number
    DateYear = Format(NormalDate, "yy")

    'Find the day number for NormalDate
    JulianDay = Format(Str(NormalDate - DateValue("1/1/" & _
    Str(DateYear)) + 1), "000")

    'Combine the year and day to get the value for JulianDate.
    JulianDate = DateYear & JulianDay

    'Display the new date in the Julian date format.
    MsgBox "The equivalent Julian date is " & JulianDate
End Sub

'This procedure will return the number of days that
'have passed since January 1 of the current year.
Sub DaysPassed()
    Dim DayOfYear As Integer          'Number of the current day

    DayOfYear = Int(((Now / 365.255) - (Year(Now) - 1900)) * 365.255)

    'Display the value of DayOfYear
    MsgBox "Day of the year:  " & DayOfYear
End Sub

'This procedure will return the number of days left in the current

Sub DaysLeft()
    Dim JanNextYear As Date         'January 1 of next year
    Dim JanCurrentYear As Date      'January 1 of current year
    Dim DayNum As Integer           'Number of the current day
    Dim DaysLeftInYear As Integer   'Days left in the current year

    JanNextYear = DateSerial(1 + Year(Now), 1, 1)
    JanCurrentYear = DateSerial(Year(Now), 1, 1)
    DayNum = Int(((Now / 365.255) - (Year(Now) - 1900)) * 365.255)
    DaysLeftInYear = JanNextYear - JanCurrentYear - DayNum

    'Display the value of DaysLeftInYear
    MsgBox "Days left in the year:  " & DaysLeftInYear
End Sub

REFERENCES

For more information about the DateSerial function, choose the Search button in Visual Basic Help, and type:

   DateSerial