Convert String to date via Code

I have a large sheet with several columns that list date time like this

0820 (5/3)

I'd like to search the entire sheet and anytime a cell has a date with this format convert it to
05032010 :0820

Anyone have coding they can share with me?

Thanks
Steve

If I were to look at 0820 (5/3) I would not know that is a date. How would your code know?

If all these numbers that are actually dates were in the same column, and nothing else was in that column, the program could assume that whatever it finds is a date and format it accordingly.

Are these all in the same column or spread willy nilly all over the sheet?

Quote:

---

Originally Posted by Hack

If I were to look at 0820 (5/3) I would not know that is a date. How would your code know?

If all these numbers that are actually dates were in the same column, and nothing else was in that column, the program could assume that whatever it finds is a date and format it accordingly.

Are these all in the same column or spread willy nilly all over the sheet?

---

They are all in the same column.

The column heading helped me to determine that it was a time.

I thought I could use Mid() but I don't know how to handle single vs double digits like
0820 (5/3) or 0820 (10/11)

Quote:

---

I thought I could use Mid() but I don't know how to handle single vs double digits like
0820 (5/3) or 0820 (10/11)

---

The simple/quick answer to that would be to determine the length, which would also have to assume that the time is always 4 digits...

Code:

---

Dim date1 as string
Dim date2 as string
date1 = "0820 (10/11)"
date2 = "0820 (5/11)"

Len(date1) 'this would return 12
Len(date1) 'this would return 11

---

so by determining the length you can determine which portion you need to extract.

are your dates (dd/mm) or (mm/dd)? as both are ambiguous it is hard to tell

though i guess it does not matter, try like

vb Code:

mystr = "0820 (5/3)"
datestart = InStr(mystr, "(") + 1
sep = InStr(mystr, "/") + 1
datend = Len(mystr) - 1
mydate = Format(Mid(mystr, datestart, sep - datestart - 1), "00") & _
Format(Mid(mystr, sep, datend - sep + 1), "00") & Year(Now) & " :" & Left(mystr, 4)
msgbox mydate