Microsoft Excel - Phone rprt displays 1 Duration as 38:19:00. Need to disp as 38m/19s. fx1/1/1900 2:19:00 PM

Asked By Bobbie Payne on 21-Nov-12 11:31 AM
Phone report for a specific customer has 57 entries. One of the duration cells is 38:19:00. When I look at the formula for this cell it displays 1/1/1900  2:19:00 PM. When I select another cell 6:26 it displays 6:26:00 AM. I have tried changing the Custom view to [mm]:ss in order to fix 38:19:00 to mins and secs amongst numerous other data without success. This should display as 38mins/19secs. Thanks.
Donald Ross replied to Bobbie Payne on 21-Nov-12 12:26 PM

Good afternoon, After looking at your cell of 38:19:00 and seeing that you state the 'formula line contains the following 1/1/1900 2:19:00 I have to assume that this number is either typed into the cell or cut and pasted from somewhere else and not a true formula. (this means the value in the cell is defined as custom or time)  and not text

I have looked at several way to modify the entry but the format 38:19:00 places the 38 in the [H] hours place when dealing with time formatting and I to am having a diffacult time correcting the format as long as it is in the cell as custom.

So my question is where do you get the original information from and how is it being placed in the sheet/cell?
perhaps we can correct this with a formula on another sheet or insert a colum in your sheet to correct this.


Harry Boughen replied to Bobbie Payne on 21-Nov-12 02:02 PM
Hello Bobbie,

That time looks like 38 hours 19 minutes to me.  An awfully long telephone conversation?


Bobbie Payne replied to Donald Ross on 21-Nov-12 03:04 PM
Original data is pulled as a phone report generated from our "not user friendly" internet phone system. It creates an Excel doc as call_detail.csv. Even saving it as an Excel Worksheet has been a dead end.

I have even tried without result in copying/pasting just the data without success.

Also, I have discovered if I select any cell with a time of 13:xx or more, the fx field displays/converts to standard time, i.e. 1:xx PM.
Donald Ross replied to Bobbie Payne on 21-Nov-12 04:22 PM
Bobbie try this.

Create a second colum right next to this one.  in this colum do two things.  one format the cell to custom mm"min":ss"sec" this will add the test min and sec to your value and that is under right click formatting.
second put =A41/60 assuming that your values are in colum a.   meaning divide 38:19:00 by 60 to get 0:38:19



Harry Boughen replied to Bobbie Payne on 21-Nov-12 09:23 PM
Hello Bobbie,

Surely if you want that to be 38 minutes 19 seconds it has to be 00:38:19.


Donald Ross replied to Bobbie Payne on 22-Nov-12 09:44 AM

Bobbie Payne replied to Donald Ross on 26-Nov-12 10:29 AM
Don -

Success with changing cell to 38m 19s, but I need to add the Duration column for a total and the 38m 19s does not add in properly. Seems as though this should be alot simpler than it is.

I can convert all entries into seconds, however the one stickler 38:19:00 converted to 38sec19min will not convert to a text entry in seconds.
Donald Ross replied to Bobbie Payne on 27-Nov-12 11:20 AM

if you could send me or post a portion of your file for us to work with it might help.  I understand sometimes you cant do this.

As for it adding up or changing to text entry I am not sure how to answer that, since we used custom formatting to get the 38m19s 

So again if you could upload an example or perhaps send me something I would be glad to see if I can help.

Bobbie Payne replied to Donald Ross on 29-Nov-12 01:45 PM
Don -

I really appreciate all of your help. I've put this one to bed. In adding the column I discovered the one cell in question actually added as mins & secs even though it is viewed as hrs/mins/secs. I'm moving on to my next big issue for call center call metrics.

Thanks again and best to you.