I exported a phone bill to CSV format and opened in Excel. How do I sum up
a column of call times that are written as hh:mm?
Note that I am not adding on a 24 hour clock. The number of hours can be
unlimited, as when you might have 1000 phone calls that were each more than
30 minutes long.
hi
if the times came in as numbers, it's just straight addition ie
=sum(A1:A1000) assuming that the times are in column A. you might need
custom formats to display it like you want.
hh:mm:ss
with the above format the hours will total while the seconds will roll
into minutes and minutes into hours.
dd:hh:mm:ss
the above format will roll the hours into days but be warned. the days
will not total past one month. when days go over 30 days, 30 days just
disappear from the formula. funny querk about microsoft's date/time
keeping system.
you can use the following formula in a second cell keying on your
first formula.
=INT(A1000)&":"&TEXT(A1000-INT(A1000),"hh:mm:ss")
but the above formula will be text so you can't use in to do math.
display only. but the days will displays past 1 month. use the first
formula for math. even though the correct days are not displayed past
1 month, they are still there in the background.
if your times didn't come in as numbers, then enter a one in a cell
off the the side. copy it. highlight the time range then pastespecial
multiply. this should turn the text numbers into number numbers.
good luck
regards
FSt1