Excel Cell Format - Is there a way to force minutes with no hours?

  • Thread starter Thread starter ebooher
  • Start date Start date
E

ebooher

What I am looking for might be a little confusing. But I need a way t
handle computations of time from another system. What is happening i
this:

I output from a system that keeps track of all time for all hour
worked for all agents on my floor. This is usually hundreds of times
with more than 12 sets per agent per day worked. Many of them ar
pre-averaged by the system itself.

For example, we keep track of how long the average call time for a
agent is, so it's already averaged from all the calls taken for the da
and output as 12:17 for average call time.

The system outputs the raw numbers into a tab delimited text file fo
importing into Excel. When excel reads the data into the system, i
inputs as 12 Hours, 17 minutes. However, the 12:17 should actually b
00:12:17. Or 0 Hours, 12 Minutes, 17 Seconds. The system itself doesn'
have a way to force the output of trailing 0's for hours. However, i
something in the field is an hour, it's 1:30:00, or 1 Hour, 30 Minutes
0 Seconds.

I need someway to force excel to assume a zero hour unless prespecifie
by the data itself, and I have not found anyway to get excel to forma
the data to force zeros into the hours field.

Any ideas
 
Hi,

Here is one way to do it. It involves quite a few steps, so you may want to
create a macro.

- import the time column as Text, not General
- assuming the time values are in column A, insert a new column B
- in B1 enter
=IF(LEN(A1)<6,"00:"&A1,A1)
- fill down column B to the bottom of the table
- select column B, then Copy, then Edit>Paste Special>Values>OK
- select any empty cell and Copy
- select the used part of column B and Edit>Paste Special>Add>OK
- without changing the selection Format>Cells>Time>OK
- delete column A

HTH
Anders Silven
 
Back
Top