how to add time in Excel spreadsheet

culabula88

I have to download my phonecalls from o2.ie to the excel spreadsheet and now I want to add the colums for the time to see how much time I spent on the phone this month and also the columns for the amount of money spent.

example

Number Time cost
087...... 00:01:02 0.15
086..... 00:02:04 0.19

Norfolknway

I added some hours worked recently and formatted the column by right clicking the letter of column required and using custom format hh:mm:ss from the list. The sum function should add up the times then in hours mins and secs.

Hope this is of some help.

culabula88

tried that but it doesnt work.

Donald-Duck

It works with the date/time format anyway, it just ignores the date.
I just did it there to test it and definitely works

littlewopper

Hey,,,, just tried the method sugessted by norfolknway, and it worked..... just right click on the column needed... selected time option and it worked using the sum operation .... I'm using office 2003- dont know if it works on other editions....if your added calls sum up to 24 hours plus (i've no idea of ur call history) the time is overlaped....ie days are not counted.... you could try to customise your own function...

lw

samhail

i have a gorgeous function that will do it !
i can only describe it as gorgeous cos it nearly made me cry figuring it out.

let me get it out of my work files for you.

samhail

the code for the weekly total is as below
=INT(ROUND(((F4-INT(F4))+(F5-INT(F5))+(F6-INT(F6))+(F7-INT(F7))+(F8-INT(F8))+(F9-INT(F9))+(F10-INT(F10)))*24,2)) & "hrs " & ROUND((ROUND(((F4-INT(F4))+(F5-INT(F5))+(F6-INT(F6))+(F7-INT(F7))+(F8-INT(F8))+(F9-INT(F9))+(F10-INT(F10)))*24,2)-INT(ROUND(((F4-INT(F4))+(F5-INT(F5))+(F6-INT(F6))+(F7-INT(F7))+(F8-INT(F8))+(F9-INT(F9))+(F10-INT(F10)))*24,2)))*60,0) & "min"





this will handle going over the 24 hours by converting the time to decimal, then adding it up, and formatting it correctly.
(i use it for work weekly time)

Can you figure out what the code does ? or want me to break it down a bit more ?

culabula88

Still doesnt work and I dont know why

I have posted up the file and Im using office 2003. Probably something very obvious wrong

Donald-Duck

Which box are you adding them up in?

culabula88

trying to add them up at the end. I havent actually put in the sum formula in a cell in the spreadsheet but I tried it before and it didnt work.

Norfolknway

I've tried adding them up in the example but doesn't work but if I type them in again exactly the same it works - very strange
btw if the total goes over 24 hours use [h]:mm:ss to get a correct total of the hours otherwise its just goes back to 0 after 24

samhail

Yeah i copied all the values out into a notpad, and copied them back into a new sheet and the sum did work
(might be something with the formatting)

I tried implementing my function there... but didnt get it fully through

Norfolknway's looks good though.

all 2 posts posted here ! welcome to boards

Norfolknway

thanks for the welcome samhail - everyone has to start somewhere:)

Steffano2002

I'm using OpenOffice at home so I'm not sure if it's the same in MS Excel but when I downloaded your "example" s/sheet I noticed that you had a character in front of all the value in Column A. As a result you will not be able to format these properly to hh:mm:ss and you won't be able to SUM them up either...

Select Column A then Ctrl + h (Find & Replace) Value Replace With [Nothing]. Then you will be able to add all the values in Column A together.

Hope the above makes sense...

thepensioner

Hi there,

Not sure if I've got this right but it seems to be working for me. See attached spreadsheet, column D, its the simplest way I can find to do it. (Hopefully I've attached it correctly).

Hope this helps.