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 sizbut on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Help formatting time duration following import of data

Status
Not open for further replies.

kentwoodjean

Technical User
Oct 19, 2002
376
US
I checked several other threads and FAQ, having tried a few things, but they just don't fit my situation.

I have a telephone file that I download from Crystal Reports to Excel where I run a macro to clean up the file. I then import into an Access table. The duration of each call is tracked in hours, minutes and seconds. For instance, 00:00:00. When I move this data from Excel to Access (currently cut and paste) it formats 00:00:00 as long as the data type is Text. Because I want to to perform an average calculation on a report, I tried to change the data type to a number or continue using Text, using an imput mask of hh:nn:ss. No matter what I do, the import to Access from Excel will not occur unless the data format is a Text file without imput mask. If I imput as Text, then my report will not allow me to do the average calculation. Am I missing something?



 
In Excel, convert the hours, minutes, and seconds to the fraction of a day they represent. If you import this decimal number to a date/time field in Access, it should show the correct hours, minutes, and seconds and you'll be able to sum or average the times (you'll have to format the results).

DayFraction = hours/24 + minutes/(60*24) + seconds/(3600*24)


 
Now I may sound a bit dense on this but please bear with me. The hh/nn/ss are currently formatted in excel something like 01:23:01. In excel, under format cell, it shows the current number category to be "General" meaning it has no specific number format.

I assume I was to use your formula above to covert my Excel # to a fraction of a day or, =01/24+23/(60*24)+01/(3600*24). Answer is 0.057640046. I did a couple of these and then tried to drag the cells down so the formula could be used in my long list of #'s, and they just kept repeating themselves. I can't believe I would need to go line by line?? In any event, I took this number and brought it over to the DBase where the field was showing date/time, and the record went in without an error. Problem is that I formated like 12:24 PM, which of course is wrong. Couple of questions: (1) Was I doing it the way you explained
(2) if that is the formula I use for Excel, must I go line by line and enter it? Sure seems like a lot of work.
 
w/o getting into the details of reporting, it seems like a bit of hte long way 'round the barn' to g through the process as you describe it. There is little or nothing which can be done in Excel that cannot just as readily be done in Ms. A., so the trip through spreadsheet land seems, to me, to be just a 'senic bypass' type of detour. I would import the file directly into a temporary recordset in Ms. A., do the V&V on the temporary recordset and then update the production recordset with the valid records.




MichaelRed
m.red@att.net

Searching for employment in all the wrong places
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top