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 !

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

Dim effdate
effdate = (100000 + (Right(Year(Date), 2) & Format(Date -
DateSerial(Year(Date), 1, 0), "000")))


--
__________________________________
HTH

Bob

"Eric @ BP-EVV" <(E-Mail Removed)> wrote in message
news:1997E278-2987-494B-8326-(E-Mail Removed)...
> 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 !

why not use excel to get the value and put it in a variable to pass to the
AS400 code?

Dim a As Variant
Range("a1").Select
ActiveCell.FormulaR1C1 = _

"=(RIGHT(YEAR(TODAY()),2)&TEXT(TODAY()-DATE(YEAR(TODAY()),1,0),""000""))+100000"
a = ActiveCell.Value
Selection.ClearContents

u can use the variable a to pass the result.

HTH


"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 - 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
>