VBA for Julian Date

[FFischer]

I needed VBA code for turning today into a Julian Date. I finally got it to work and thought I would share...

Sub Julian()

Dim AWB As WorksheetFunction, JulianDate As String
Set AWB = Application.WorksheetFunction

JulianDate = AWB.Text(AWB.Days(Date, 1) - AWB.Days(DateSerial(Year(Date), 1, 0), 1), "000")

MsgBox JulianDate

End Sub

 

[Scott Huish]

You shouldn't need to make any worksheet function calls, and there is no worksheet function called Days:

Sub Julian()
Dim dt As Date
dt = Date
MsgBox Year(dt) & Format(dt - DateSerial(Year(dt), 1, 1) + 1, "000")
End Sub

Drop the Year(dt) &
if you don't want the year

 

[Rick Rothstein]

You shouldn't need to make any worksheet function calls, and there is no worksheet function called Days:

Sub Julian()
Dim dt As Date
dt = Date
MsgBox Year(dt) & Format(dt - DateSerial(Year(dt), 1, 1) + 1, "000")
End Sub

Drop the Year(dt) &
if you don't want the year

Alternately...

Sub Julian()
  MsgBox Year(Date) & Format(DatePart("y", Date), "000")
End Sub

 

[Scott Huish]

Never heard of DatePart before, that is awesome!

 

[FFischer]

There is no worksheet function called Days:

There is in Excel 2013

 

[Rick Rothstein]

There is in Excel 2013

Then they must have added it in XL2013 (I don't have that version, so I cannot check it), but it did not exist in XL2010 or earlier. So... if you plan to use it, remember that no one using an earlier version of Excel will be able to use your workbook.

 

[Renatododge]

I needed VBA code for turning today into a Julian Date. I finally got it to work and thought I would share...

Sub Julian()

Dim AWB As WorksheetFunction, JulianDate As String
Set AWB = Application.WorksheetFunction

JulianDate = AWB.Text(AWB.Days(Date, 1) - AWB.Days(DateSerial(Year(Date), 1, 0), 1), "000")

MsgBox JulianDate

End Sub

I just want to verify this works for perpetual and leap year right