Convert String to Excel Date and Time, with specific string format

5
votes

I've got an Excel/VBA macro which list files in a directory.

Filenames are in format : yyyy-MM-dd@hh-mm-ss_[description].csv

for instance : 2013-07-03@22-43-19_my-file.csv

I then need to get the first part of the filename into an Excel Date object (including a Timestamp)

I found the CDate() function, but it doesn't take any "format" parameter, and the Format() function works the wrong way, ie to convert a String to a Date.

I'd like to get a function with takes a String and a format, and returns an Excel Date/Time object.

Is there a function designed to do that ?

Thanks,

1
excelvba
Take a look at DATESERIAL. – chuff
i know I can use DateSerial(y, m, d) + TimeSerial(h, m, s) but i'll need to parse my string format, determine where are the year, month etc..., extract substrings for each sub-part of the Date, and then use those two functions. Rather I'd like a simpler function with take a String and an input-format, and returns a DateTime – adrien.pain
seems, from msdn (msdn.microsoft.com/en-us/library/aa227484%28v=vs.60%29.aspx) there isn't any built-in VBA function that will suit my needs. I'll have to write one – adrien.pain
Pardon my swiss cheese memory, I meant Datevalue. There's also a corresponding TimeValue function. – chuff
@chuff no pb ;-) But DateValue() and TimeValue() only works with Excel "pre-formatted" formats, like MM/DD/YYYY and such. It won't work with my format, which is : yyyy-MM-dd@hh-mm-ss – adrien.pain

1 Answers

7
votes

Try this out:

Function ParseDateTime(dt As String) As Date
   ParseDateTime = DateValue(Left(dt, 10)) + TimeValue(Replace(Mid(dt, 12, 8), "-", ":"))
End Function