MrExcel Publishing
Your One Stop for Excel Tips & Solutions

imported time

Posted by Sturty on August 01, 2001 11:37 AM

when I paste a time value (from an html document) into an excel workbook the results can often be confusing. My problem is if the time value that is pasted is less then a minute or less then an hour. If it is pasted as :SS or :mm:ss (i.e. :33 or :13:12)it may be pasted with a general format. This causes several difficulties when trying to find the average time for something.(In this case hold time in a phone report). Changing the Format could cause the minutes to be placed in the hour column (13:12:00 rather then 00:13:12 [this could make a CEO wonder why people are being put on hold for 13 hours].) Is there a solution to this?

Posted by Ian on August 01, 2001 1:07 PM

not entirely sure what you mean?

I'm assuming your getting the data from a dialer log, or by some other split skill/intervel reporting package like CentreVu or similar

what I do at work is have a raw data sheet that the report is cut/pasted into (always the same starting cell), then I play around on the reporting sheet that links to it, until I've got the desired result. You can then Hide the sheet and use the polished Report sheet for printing, sending etc.

you could do your calculations on the Report sheet.


this will make hours = minutes
minutes = seconds etc
=sum(Whatever)*60 will do the opposite

also =sum(Whatever)*3600

will turn 00:00:13 being hh:mm:ss
into 13:00:00

Also by using a hidden sheet and spending time on linking correctly and formulas, your limited by the layout of the original. you can then go on to adding more columns and put percentages of Typing to hour worked, Idle time or average Dails/hr

hope this helps.


Posted by Sturty on August 01, 2001 1:36 PM

I am using CentreVU Supervisor and I am cutting/pasting into a seperate speadsheet so I can make everything pretty. and yes that does help alot thank you, it will make averages and formulas easier to chew on.


Posted by Ian on August 02, 2001 12:51 AM

Surely CentreVu Superviser is the Superviser? :)

Posted by Ian on August 02, 2001 12:52 AM

Try that again: Surely CentreVu Supervisor is the Supervisor? :)