You are currently offline, waiting for your internet to reconnect

Visual Basic Procedures for Julian Date Calculations

This article was previously published under Q116281
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.
SUMMARY
This article describes how to convert between the serial date, normal date,and Julian date formats using Visual Basic, Applications Editionprocedures.
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 1900date system.

Julian dates are used to show the number of days that have passed and/orthat are left in a given year. The format for a Julian date is thefollowing:
yyddd
Where 'yy' is the year and 'ddd' is the day of the year. Below are examplesof 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, Serialto Julian, and Normal Date to Julian. The last two procedures show how tofind the day of the year for the current date and how many days are left inthe 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 " & SerialDateEnd 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 " & JulianDateEnd 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 " & JulianDateEnd 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:  " & DayOfYearEnd Sub'This procedure will return the number of days left in the currentyear.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:  " & DaysLeftInYearEnd Sub				
REFERENCES
For more information about the DateSerial function, choose the Searchbutton in Visual Basic Help, and type:
DateSerial
97 4.00 4.0a XL
Properties

Article ID: 116281 - Last Review: 10/11/2006 00:48:43 - Revision: 2.2

  • Microsoft Excel 97 Standard Edition
  • kbprogramming KB116281
Feedback