I have inherited a database in which there is a time duration field which is a text field. I have tried converting the data type to date/time format hh:nn but it threatens to delete the contents of the field. I need it to be time so that I can perform calculations on it.
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
convert text to time 1
- Thread starter tizwaz
- Start date
PaulBricker
Programmer
Somethings not right because it works great for me. You are trying this on the field MyTime and not LatestTime? Correct?
Paul
Paul
- Thread starter
- #22
PaulBricker
Programmer
Try this
=Int(Sum(CDate([MyTime])))*24+DatePart("h",Sum(CDate([MyTime]))) & ":" & DatePart("n",Sum(CDate([MyTime])))
The other one bombed over 24 hours. This one works above 24 hours.
Paul
=Int(Sum(CDate([MyTime])))*24+DatePart("h",Sum(CDate([MyTime]))) & ":" & DatePart("n",Sum(CDate([MyTime])))
The other one bombed over 24 hours. This one works above 24 hours.
Paul
If you need to have the code calculate how many hours
duration = CDbl(Left([Time taken],2)) + CDBL(Right([Time taken],2)/60
Then to convert this Hour calculation to a Date/Time format, use
MyTime = CDate(duration / 24)
Be aware, CDate is going to think that this value is a date/time value with December 30, 1899 having a date value of 0, January 1, 1900 with a date value of 2 and so on.
after converting this number, you can still keep adding to it, but then once you have your total time, you can then do the following:
Dim strTotalTime As String
strTotalTime = CSTR(Int(MyTotalTime*1440) mod 60)
strTotalTime = CSTR(Int(MyTotalTime*24)) & ":" & _
Left("0",2-Len(strTotalTime)) & _
strTotalTime
Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000
duration = CDbl(Left([Time taken],2)) + CDBL(Right([Time taken],2)/60
Then to convert this Hour calculation to a Date/Time format, use
MyTime = CDate(duration / 24)
Be aware, CDate is going to think that this value is a date/time value with December 30, 1899 having a date value of 0, January 1, 1900 with a date value of 2 and so on.
after converting this number, you can still keep adding to it, but then once you have your total time, you can then do the following:
Dim strTotalTime As String
strTotalTime = CSTR(Int(MyTotalTime*1440) mod 60)
strTotalTime = CSTR(Int(MyTotalTime*24)) & ":" & _
Left("0",2-Len(strTotalTime)) & _
strTotalTime
Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000
- Thread starter
- #25
Paul
Brilliant - its working!! Found out why I was getting the data type mismatch - there were 2 records with no time just showing as : When I deleted them and used your latest function it works.
There shouldn't really be any closed calls that took no time these must be errors. Will have to change something to stop it happening.
Thanks again for your patience and help. Thanks everyone else as well - I'll read through the suggestions - I'm learning all the time with this forum!!
Brilliant - its working!! Found out why I was getting the data type mismatch - there were 2 records with no time just showing as : When I deleted them and used your latest function it works.
There shouldn't really be any closed calls that took no time these must be errors. Will have to change something to stop it happening.
Thanks again for your patience and help. Thanks everyone else as well - I'll read through the suggestions - I'm learning all the time with this forum!!
PaulBricker
Programmer
OK. Glad we put that one to bed. Strings to Date/Time are always a bit of a problem.
Paul
Paul
- Status
Similar threads
- Locked
- Question
- Locked
- Question
- Locked
- Question
- Locked
- Question
