Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
date conversion....
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 ! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
date conversion....
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
date conversion....
Dim effdate
effdate = (100000 + (Right(Year(Date), 2) & Format(Date - DateSerial(Year(Date), 1, 0), "000"))) -- __________________________________ HTH Bob "Eric @ BP-EVV" wrote in message ... 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 ! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
date conversion....
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 ! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
date conversion....
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Date Conversion | Excel Discussion (Misc queries) | |||
Date Conversion | Excel Discussion (Misc queries) | |||
Date Conversion | Excel Discussion (Misc queries) | |||
Date conversion | Excel Programming | |||
Date Conversion | Excel Discussion (Misc queries) |