Excel Macro (VB)... 12/15/2003 to 15DEC03

[PhoenixOfWater]

I'm trying to make a macro that will that a date in is format "12/15/2003" and make in to the format "15DEC03"

but i can't get the "InStr" to work righ when trying to get the number between the "/"
Like:
Month = 12
Day = 15
Year = 03

but i get something like:
Month = 2/
Day = /1
Year = 03

here the part of the code that i think i have wrong

PosMonth = InStr(1, DT, "/", 1)
PosDay = InStr(PosMonth, DT, "/", 1)

Day = MID(DT, PosMonth, PosDay)
Month = Left(DT, PosMonth - 1)

Also is there a way to make a String in to a Int?


here what i have so far... i know there more i need to do like set up array with the name of the months but i'll be able to do that after i get the months and days working right

Sub FTmover()
' FTmover Macro
'
'

Dim Row As Integer
Dim FileName As String
Dim MID1 As String
Dim DT As String
Dim Day As String
Dim Month As String
Dim Year As String
Dim PosMonth As Integer


Row = 2

Do While Not IsEmpty(Cells(Row, 1))

MID1 = Cells(Row, 1)
DT = Cells(Row, 2)

PosMonth = InStr(1, DT, "/", 1)
PosDay = InStr(PosMonth, DT, "/", 1)

Day = MID(DT, PosMonth, PosDay)
Month = Left(DT, PosMonth - 1)
Year = Right(DT, 2)


Row = Row + 1
Loop


End Sub

 

[BitSpit]

An alterative would be to use a custom date format. Format Cells>Numbe>Custom. Type ddmmmyy as the type. That will produce a date like 15Dec03. If you need all caps, just use the appropriate function.

 

[noxxic]

s = "12/15/2003"
i = InStr(s, "/")
j = InStr(i + 1, s, "/")
xMonth = Left(s, i - 1)
xDay = Mid(s, i + 1, j - i - 1)
xYear = Mid(s, j + 1)

I don't think VB is very strict about variable types, so you can use either of these like an integer. But if that doesn't work, try casting it in INT().

 

[PhoenixOfWater]

Originally posted by: BitSpit
An alterative would be to use a custom date format. Format Cells>Numbe>Custom. Type ddmmmyy as the type. That will produce a date like 15Dec03. If you need all caps, just use the appropriate function.

Yeah that would make it look right but the "Cell" would still equal 12/15/2003

 

[PhoenixOfWater]

Originally posted by: noxxic

s = "12/15/2003"
i = InStr(s, "/")
j = InStr(i + 1, s, "/")
xMonth = Left(s, i - 1)
xDay = Mid(s, i + 1, j - i - 1)
xYear = Mid(s, j + 1)

I don't think VB is very strict about variable types, so you can use either of these like an integer. But if that doesn't work, try casting it in INT().

Thank you very much!!! thats works very good