julian date

[Guest]

Is there a formula or line of code that, in a date cell, would automatically
convert a date entered in the 07/16/04 format into the 04188 julian format?

thank you.

 

[JE McGimpsey]

You can't have a formula in a cell and make an entry in it - the
formula is overwritten. You can use an event macro:

Put this in the worksheet code module (right-click the worksheet tab and
choose View Code):

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim nYear As Long
Dim nDays As Long
With Target
If .Count > 1 Then Exit Sub
If Not Intersect(.Cells, Range("A1")) Is Nothing Then
If IsDate(.Value) Then
nYear = Year(.Value)
nDays = CDate(.Value) - DateSerial(nYear, 1, 0)
.NumberFormat = "@"
Application.EnableEvents = False
.Value = Format((nYear Mod 100) * 1000 + nDays, "00000")
Application.EnableEvents = True
End If
End If
End With
End Sub


This will produce pseudo-Julian dates (for real Julian dates, see

http://aa.usno.navy.mil/data/docs/JulianDate.html

)