convert text to time 1

[tizwaz]

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.

 

[PaulBricker]

Somethings not right because it works great for me. You are trying this on the field MyTime and not LatestTime? Correct?

Paul

 

[tizwaz]

Wish I knew what - yes I'm using MyTime. Have you recreated what I'm trying to do - ie started with a text field etc. Has it summed correctly even when over 24 hours?

 

[PaulBricker]

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

 

[rdodge]

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

 

[tizwaz]

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!!

 

[PaulBricker]

OK. Glad we put that one to bed. Strings to Date/Time are always a bit of a problem.

Paul