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 !