Visual Basic Procedures for Julian Date Calculations

Article translations Article translations
Article ID: 116281 - View products that this article applies to.
This article was previously published under Q116281
Expand all | Collapse all

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. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers 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 requirements. 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
Where 'yy' is the year and 'ddd' is the day of the year. Below are examples of serial, normal, and Julian date equivalents:
   Serial date          Normal date          Julian date
   -----------          -----------          -----------
   34335                Jan-01-1994          94001
   34699                Dec-31-1994          94365
				
The following Visual Basic procedures convert from Julian to Serial, Serial to Julian, and Normal Date to Julian. The last two procedures show how to find the day of the year for the current date and how many days are left in the year.
'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 = Round(((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
year.
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 = Round(((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

Properties

Article ID: 116281 - Last Review: October 11, 2006 - Revision: 2.2
APPLIES TO
  • Microsoft Excel 97 Standard Edition
Keywords: 
kbprogramming KB116281
Retired KB Content Disclaimer
This article was written about products for which Microsoft no longer offers support. Therefore, this article is offered "as is" and will no longer be updated.

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com