Calculating Time in Access. How to derive minutes? 2

[Focusfocs]

Hi,
I have imported a file from excel which has a field "Avg_Time" which contains an amount of time it takes to drive to a destination. The Avg_Time field was imported as a Date/Time field. When the data was in excel, all was peachy with the appropriate data that represented the time it took to get to a specified destination. The contents were along the lines of 0:31 0:59 1:02 to specify how long it took to get to that destination. When it was imported as a time field into Access 2003, the data got converted into 12:31AM 12:59AM 1:02AM ...The question is: How do I specify an amount of time rather than a date/time field? Is to ask, how do I get my minutes back?? And then, if one to calculate the result of that (for example if I have to sum the amount of times in the column) will I have to do any 'fancy footwork' in a query to achieve this?
Thanks

 

[GingerR]

is :31 :59 1:02 all in one single cell? or is this three diff examples (three different rows of data)?

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[Focusfocs]

Hi! Thanks for responding. The examples are in different rows repectively. The :31 and so forth would represent 31 minutes but when it was imported into Access from Excel it turned it to 12:31; if the data 1:02 it was to represent 1 hour and 2 minutes...again, the coversion turned it to 1:02AM....

 

[PHV]

Set the display format as "hh:nn".

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[GingerR]

Hi. Make your data type Date/Time and change the format to SHORT TIME. This will display it as :31, 1:02, etc.

Then make a function in a module sheet:

Function TimeToMin(mytime)
TimeToMin = CInt(CDbl(mytime) * 24 * 60)
End Function

Then use that function in a query

=TimeToMin(MinuteFieldName)

(reference =

http://support.microsoft.com/kb/q141536/)

and it will convert it to minutes. You can also make a new field in your table and write an update query to just go thru and store the converted value. It depends on how often you are importing the Excel stuff and what else is going on in your db to how you might want to do it.

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[Focusfocs]

Thank you both so very much. Using both examples I have successfully accomplished what I set out to do!!