Formatting a Time Duration in Excel

[sandybam]

Hi All You Experts!

I need some guidance on how to format time duration. I have a file that I have imported from our phone system directly into Excel. The duration of each call should be tracked in hours, minutes and seconds. For instance, 00:00:00 should say that this Employee was not on the phone today. Instead Excel says this is 12:00:00 AM. I have tried to create a custom format to no avail. The only other thing I can think of is splitting this cell into three separate cells (hours, minutes, and seconds). Ultimately once the formatting is corrected I will then be creating a macro in Access to automatically transfer the data into Access.

Any thoughts on what I should do?

Thank you!

 

[prajac]

I don't know if this will be of any help but have you tried the the format for elapsed time. There is one in custom - [h]:mm:ss. The square bracket denotes elapsed time. Hope this is of help

 

[euskadi]

SandyBam - Kinda off on a tangent here... what type of phone system are you using? There might be an easier way of getting the data into Access.

 

[sandybam]

euskadi,

We use a Meridian/ACD system. I receive a file that can only be opened using Notepad or Excel.

 

[euskadi]

My condolences... just kidding. ;-) I've worked with Lucent (Avaya) G3s and Rockwell Spectrum, and I know it's possible to use ODBC to get the data right from those ACDs right into Access. The only work I've done with Meridian involved a system that was locked down so tight that we had to type our numbers in manually from a print-out.

One thing you might try is doing a text transfer. Access does do an OK good job of parsing out text files if you take the time to set up the specifications right. I'm not saying it will work for sure, but if Excel will open the file then there's hope for Access.

Good luck!! (And buy an Avaya!)