date conversion....

[Eric @ BP-EVV]

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]

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

 

[Bob Phillips]

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

 

[dmoney]

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]

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

Thanks !

Eric