Can someone help me with some vba code for the date function?

what i'm trying to accomplish is to convert the string 20050401 to
04/01/2005.

The formula =DATE(LEFT(E7,4),MID(E7,5,2),RIGHT(E7,2)) works fine for that.
I would like to put it in vba. I've written:

Function new_date(last_done_date) As String
'returns New last_done_date
new_date = Application.Date(Left(last_done_date, 4), Mid(last_done_date, 5,
2), Right(last_done_date, 2))
End Function

But it returns a value error.

last_done_date is the range of cells that contain the date string - 20050401

Thanks,

Brian

You almost the Demo'd from the imediate window.

e7 = "20050401"
? DATEserial(LEFT(E7,4),MID(E7,5,2),RIGHT(E7,2))
04/01/2005

for simplicity, I made E7 a variable and assigned you string to it. Then
Used the worksheet formula you show, adjusted for VBA.

the Left, Mid and Right functions are not the same ones in Excel, but are
the VBA versions which behave similarly.

--
Regards,
Tom Ogilvy



"Brian Rogge" <brian -at- minnplace dot com wrote in message
. ..
Can someone help me with some vba code for the date function?

what i'm trying to accomplish is to convert the string 20050401 to
04/01/2005.

The formula =DATE(LEFT(E7,4),MID(E7,5,2),RIGHT(E7,2)) works fine for that.
I would like to put it in vba. I've written:

Function new_date(last_done_date) As String
'returns New last_done_date
new_date = Application.Date(Left(last_done_date, 4), Mid(last_done_date,
5,
2), Right(last_done_date, 2))
End Function

But it returns a value error.

last_done_date is the range of cells that contain the date string -
20050401

Thanks,

Brian

On Mon, 23 May 2005 15:07:29 -0500, "Brian Rogge" <brian -at- minnplace dot
com wrote:

Can someone help me with some vba code for the date function?

what i'm trying to accomplish is to convert the string 20050401 to
04/01/2005.

The formula =DATE(LEFT(E7,4),MID(E7,5,2),RIGHT(E7,2)) works fine for that.
I would like to put it in vba. I've written:

Function new_date(last_done_date) As String
'returns New last_done_date
new_date = Application.Date(Left(last_done_date, 4), Mid(last_done_date, 5,
2), Right(last_done_date, 2))
End Function

But it returns a value error.

last_done_date is the range of cells that contain the date string - 20050401

Thanks,

Brian


It might be better to convert the 20050401 to an Excel date, rather than a
string.

======================
Function new_date(last_done_date) As Date
'returns New last_done_date
new_date = CDate(Format(last_done_date, "0000\/00\/00"))
End Function
=================

You'll need to format the cell in which you have this function as a Date.


--ron

That did the trick. Thank You!

I did not see a reference to the dateserial function anywhere. Though I
suppose if I search for dateserial I'll find a ton now. Gonna have to buy a
book i think.

As for formatting the original string, suggested by Ron, I'll get to that
eventually. The source data is stored in a btrieve database and that is the
format. It has worked this way with no problem but the spreadsheet is too
big now so i'm converting the formulas to vba.

Thanks for your suggestions.

R/

Brian


"Brian Rogge" <brian -at- minnplace dot com wrote in message
. ..
Can someone help me with some vba code for the date function?

what i'm trying to accomplish is to convert the string 20050401 to
04/01/2005.

The formula =DATE(LEFT(E7,4),MID(E7,5,2),RIGHT(E7,2)) works fine for that.
I would like to put it in vba. I've written:

Function new_date(last_done_date) As String
'returns New last_done_date
new_date = Application.Date(Left(last_done_date, 4), Mid(last_done_date,
5, 2), Right(last_done_date, 2))
End Function

But it returns a value error.

last_done_date is the range of cells that contain the date string -
20050401

Thanks,

Brian