Date formatting

[Brian Rogge]

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

 

[Tom Ogilvy]

You almost there: 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.

 

[Ron Rosenfeld]

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

 

[Brian Rogge]

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