XL: Visual Basic Procedures for Julian Date Calculations

ID: Q116281

The information in this article applies to:

• 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 provides programming examples 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 article assumes that you are familiar with the programming language being demonstrated and the tools used to create and debug procedures. Microsoft support professionals can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific needs. If you have limited programming experience, you may want to contact the Microsoft fee-based consulting line at (800) 936-5200. For more information about the support options available from Microsoft, please see the following page on the World Wide Web:

   http://www.microsoft.com/support/supportnet/refguide/

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

'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

Keywords          : kbprg kbdta KbVBA 
Version           : WINDOWS:5.0,5.0c,7.0,97
Platform          : WINDOWS

Last Reviewed: May 17, 1999