The data I'm working with has a field named "DteInput" with times formatted as text -

i.e.

dteInput.........(Time would be:)
13.44.03................1:44:03 PM
02.38.20................2:38:20 AM

is there a formula, similar to Cdate() that would convert to time ?

Hi

You can format the text as you want it by using the Format function -



If you want it as Date/Time datatype you can use the CDate() function

Bakersburg,

Here's a fairly simple function that uses the DateAdd() function to add your hours, minutes and seconds together.



This will end up returning a fractional value. If you are also dealing with a separate date field, you might want to consider combining this time value with the date field value. It would be like the difference between storing 15 and 0.75 in two fields or 15.75 in a single field.

Hope this helps,

Dennis

Dennis/Alan,
Thanks ! Works great !

How can I calculated the elapsed time in hours, minutes, seconds or better, days, hours, minutes, seconds ?

say I have a field dteInput, and a text field dtecompleted ?

and I want to calc the elapsed time ?

( I attached a sample )

thanks again !!!
(IMG:style_emoticons/default/cool.gif)
Steve

Hi Steve
Had some coding in my library by Graham R Seach MCP MVP...

Here are the optional formats that the function can produce..

?Diff2Dates("y", #06/01/1998#, #06/26/2002#)
4 years
?Diff2Dates("ymd", #06/01/1998#, #06/26/2002#)
4 years 25 days
?Diff2Dates("ymd", #06/01/1998#, #06/26/2002#, True)
4 years 0 months 25 days
?Diff2Dates("d", #06/01/1998#, #06/26/2002#)
1486 days
?Diff2Dates("h", #01/25/2002 01:23:01#, #01/26/2002 20:10:34#)
42 hours
?Diff2Dates("hns", #01/25/2002 01:23:01#, #01/26/2002 20:10:34#)
42 hours 47 minutes 33 seconds
?Diff2Dates("dhns", #01/25/2002 01:23:01#, #01/26/2002 20:10:34#)
1 day 18 hours 47 minutes 33 seconds
?Diff2Dates("ymd",#12/31/1999#,#1/1/2000#)
1 day
?Diff2Dates("ymd",#1/1/2000#,#12/31/1999#)
-1 day
?Diff2Dates("ymd",#1/1/2000#,#1/2/2000#)
1 day

Have included the function in your example db...

Query result:
NewTime NewTimeAlt TimeCal
1:32:49 PM 13:32:49 6 hours 34 seconds
4:22:23 AM 04:22:23 2 minutes

For days, hours, minutes, seconds ?
Use:
?Diff2Dates("dhns", #01/25/2002 01:23:01#, #01/26/2002 20:10:34#)
1 day 18 hours 47 minutes 33 seconds

HTH's
(IMG:style_emoticons/default/thumbup.gif)

Larry,
That covers pretty much EVERYTHING !! thanks ! (IMG:style_emoticons/default/cool.gif)

Steve

(IMG:style_emoticons/default/yw.gif)

I'm sorry, I can't figure out the syntax to use that in a query - and I need the criteria to be equal to or less than 5 minutes - what would the criteria be ? Remember, my field names are dteInput and dteCompleted

Hi -

It's as simple as:



HTH - Bob

Bakersburg,

You use a User Defined Function just like any other function. If your elapsed time is the Completed time minus the Input time, it would be:

ElapsedTime: TextToTime(dteCompleted)-TextToTime(dteInput)

This is dependent upon the dteCompleted value being higher than dteInput. If you are dealing with something that starts late at night, and ends early the next morning, you'll need to include the date information, not just the time information.

Hope this helps,

Dennis