Adding Columns of Hours and Minutes

W

W

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.
 
F

FSt1

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
 
F

FSt1

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.

post scriptum
excel keeps time as a decimal value of a day. that is why it is just
straight addition. in the background, excel is just adding up a bunch
of decimal values displayed as time. formating does not change this
value, it just changes the way it looks on the screen (in the cell).
you could also reformat the time value to percent and back to time.
the underlying value does not change, just the way it looks on the
screen.
you also don't have to use my formats. i was shooting from the hip.
you might click format cell>time and snoop around the builtin time
formats provided by MS. you might find something you like better. you
might also click format cells>custom and snoop around there. if you
use my format with days, you would have to enter it in custom as a
custom format.

Regards
FSt1
 
W

W

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.

I did a straight sum, and the sum is showing as 00:00.

I formatted the cell as hh:mm.
 
F

FSt1

I did a straight sum, and the sum is showing as 00:00.

I formatted the cell as hh:mm.

hi
is your times text or numbers. can you reformat to general and have a
time show as a decimal value?
if not then excels thinks the time numbers are text and will not add.
you will have to convert the data to values(numbers). see my first
post, near the end.

regards
FSt1
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top