String to Date conversion [RESOLVED]

[bat711]

I have a string in the format of "MMMM:SS" that I import into MS Access that I would like to convert into a 'Date/Time' format and I'm not sure how to do it. Is it possible to do this and use my MMMM:SS format?

[Pino]

Let me know if the link below helps you, have a look at the conversion functions.

http://www.vbforums.com/showthread.php?t=316508

[RhinoBull]

"MMMM:SS" is very unusual format so I'm afraid you cannot convert it back to "normal" date and/or time. Sorry.

[bat711]

Well I guess this isn't really a "VB" question, but currently I'm just sending the string as text through SQL. I am trying to work with the data in Excel as well, but I'm having trouble since it is in a "Text" format I can't do mathematical calculations on it. So maybe I need to look for something in Excel to convert it.

[RhinoBull]

It doesn't really matter "where" - what you have is MONTH:SECONDS format so what are going to do with that? You cannot convert it to current date/time nor you can get day/year/hour/minutes from it ...

[bat711]

It's in a format of Minutes:Seconds, I'd just like to change it to something besides a text string so that I can do calculations with it. Excel has several formats that are similar to this and would be useful but it won't convert it. Ithink I may just not be able to change what I have.

[Max_aka_NOBODY]

I'm not a date specialist at all, but using simple maths and string parsing it's not that hard to get. Here's a sample code:

Option Explicit
Const src = "0120:10"
 
Private Sub Command1_Click()
 
Dim d As Date
Dim s() As String
 
s = Split(src, ":")
d = (CSng(s(0)) / 1440) + (CSng(s(1)) / 86400)
MsgBox d
 
End Sub

I haven't tested it except on a few values, but it should work.

[bat711]

Thanks, I didn't of converting it into days, but it works.

[Max_aka_NOBODY]

Not sure if I understood you write, but it does convert to days, so "4320:35" would be converted to [30/12/1899 + 3 days + 35 seconds]. 30/12/1899 is the lowest boundary for dates, and is equal to 0.