Excel 2003 Question

[zon123]

I have a spreadsheet report of time taken on each call for specific phones.The report shows the hr:min;Sec on the same cell. For the calls that take less than an hour the time shows as min;Sec. I would like to automatically set all cells in this column to show HR:min;Sec on all the cells where the calls that take less than an hour reflect 0(zero) for the HR. Example if a call took 27min and 40 Secs I would like the cell to show 0:27;40.
Please help.

 

[AnotherHiggins]

The first question is whether these are actually stored as times or strings right now.

In another column, ue the formula =IsNumber(A1)
[tab](where A1 is a cell containing one of these times)

fill that formula down. Does the formula return TRUE or FALSE?

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[SkipVought]

Hi,

If you have REAL TIMES on your sheet, they will be formatted h:mm:ss (COLONS)

Select one of your times and Format/Cells - Number Tab

and observe what formatting is being applied.

Why do Dates and Times seem to be so much trouble? faq68-5827

Skip,

_{[red]Be Advised![/red] A chicken, who would drag a wagon across the road for 2 cents, is…
POULTRY in motion to PULLET for a PALTRY amount!}

 

[zon123]

Hi anotherhiggins,
THey are stored as times and the formula gave me a result of "FALSE". Forgot to mention that when I select one of my times and and go to Format/Cells - Number Tab, and select the format I want, no changes are applied in the cells. The spreadsheet is not protected in anyway. I'm exportig reports from Interactive intelligence administrator. We use I3 in conjuction with Call Manager for our phone system.

 

[AnotherHiggins]

Ah. The facts that the formula returned "FALSE" and the display not changing when you go to Format>Cells both (independently) tell us that your data is not stored as times, but as strings.

We can convert your data to actual times by using the ol' 'Times One Fix' (I'll explain later) but the problem is that Excel will assume that 27:40 is Hours and Minutes, not Minutes and Seconds.

There are tons of ways around this. But the one that strikes my fancy at the moment is to use a formula in a 'helper column'. In an empty column to the right of all imported data, use this formula:
[COLOR=blue white]=if(len(A2)=5,("00:"&A2)*1,if(len(A2)=2,("00:00:"&A2)*1,A2*1))
[/color]
[tab](replace A2 with the appropriate cell reference)

The result won't look like times at all - that's ok (See Skip's FAQ). Format the cell as Custom, [h]:mm:ss. The brackets around the hours will force Excel to display the total number of hours even if the time exceeds 24 hrs. Now drag the formula down as far as necessary.

If you like, you can select the entire column then Copy, Paste Special > Values to replace the formulae with actual time info.

Give us a shout if you run into any trouble.

PS – Oh yeah, about that ‘times one fix’: You can type a 1 into any empty cell to the right of the imported data. Then copy that cell. Now select the cells that are giving you trouble (supposed to be times but aren’t) and Edit > Paste Special > Multiply & Values. That will force the little buggers into actually behaving as numbers. But, as I said earlier, that won’t work straight out in your case because Excel will just assume that 27:40 is hours & minutes. Even worse, if you ever have data that is just seconds, Excel will assume that 27 is days instead of seconds. That’ll throw off your data!

If your head is spinning right now, do yourself a favor and read FAQ68-5827 to learn about how Excel deals with Dates and Times.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.