Microsoft KB Archive/116281

= Visual Basic Procedures for Julian Date Calculations =

Article ID: 116281

Article Last Modified on 10/11/2006

-

APPLIES TO


 * Microsoft Excel 97 Standard Edition

-



This article was previously published under Q116281



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

