Dave - that's awesome....exactly what I needed.

Thanks !

Eric

"Dave Peterson" wrote:

This formula seems to do the same as your worksheet formula:

=1000*(YEAR(TODAY())-1900)+TODAY()-DATE(YEAR(TODAY()),1,0)

This worked ok in code:

Dim EffDate As Long
EffDate = 1000 * (Year(Date) - 1900) + Date - DateSerial(Year(Date), 1, 0)
MsgBox EffDate



Eric @ BP-EVV wrote:

The following formula works fine in an excel spreadsheet:

=(RIGHT(YEAR(TODAY()),2)&TEXT(TODAY()-DATE(YEAR(TODAY()),1,0),"000"))+100000

to convert today's date to my company's version of a Julian date.

10/29/2008 = 108303

I'm trying to do this calculation "on the fly" within VB code since I need
the converted date in order to execute a SQL statement against our AS/400
database to return data to Excel.

I can't seem to get it right....

dim effdate as long

effdate =
application.worksheetfunction.(RIGHT(YEAR(TODAY()) ,2)&TEXT(TODAY()-DATE(YEAR(TODAY()),1,0),""000""))+100000

doesn't seem to cut it.....any ideas ??

Thanks !

--

Dave Peterson