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

Question

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,

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
– adrien.pain, Commented Jul 3, 2013 at 21:04
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
– adrien.pain, Commented Jul 3, 2013 at 21:07
Pardon my swiss cheese memory, I meant Datevalue. There's also a corresponding TimeValue function. — chuff
– chuff, Commented Jul 3, 2013 at 21:08
@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
– adrien.pain, Commented Jul 3, 2013 at 21:11

chuff · Accepted Answer · 2013-07-03 21:47:50Z

8

Try this out:

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

answered Jul 3, 2013 at 21:47

chuff

5,9061 gold badge24 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

adrien.pain Over a year ago

yep, thanks, I'll mark this as the accepted answer, since Excel doesn't provide its own DateFormat(String date, String format) function :-(

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related