Yahoo Answers is shutting down on May 4th, 2021 (Eastern Time) and the Yahoo Answers website is now in read-only mode. There will be no changes to other Yahoo properties or services, or your Yahoo account. You can find more information about the Yahoo Answers shutdown and how to download your data on this help page.
Can I get an Excel Formula to Add Hours of all employess?
My time clock (not in excel) lists employees hours in an XX.xx format for regular and overtime hours, ie. 40.00 and 19.12. I have to complete a OT report and need to add all the times of the employees together. I need a formula to Add the time together but remain in a time format. For example, currently, if I add 19.12 to 6.54 I get 25.66 instead of 26.06. Thanks for the help.
I've been trying that but the raw data is not in time format. I have 40.00 in the cell, if I convert that to a time field it changes it to 2:52am. Say I have OT hours of 6.48 and 10.42, I need to add these together and get 17.30 total instead of 16.90.
The fields are not in Time format. It is the results of the time clock and the time clock is not in Excel. It is the results that I need to total.
5 Answers
- 1 decade agoFavorite Answer
Goto Format>cells
select number tab and select Time then choose the type of time format from the right hand box.
apply this to all cells with time data in and to the formulae cells.
Any probs leave me a message.
- 1 decade ago
Select the whole column of the data which is not in time format (because for Excel, time format should be hh:mm, NOT hh.mm). Use "Replace" command in Menu Edit to replace "." with ":" to change the data into time format. Then Menu Format -> Cells -> Number -> Category. Choose "Custom" under Category. In the box "Type" on the right, you type "[h]:mm" and then click OK. Now, you can sum the data. Make sure to format the cell in which you put the sum to the same time format.
- Typing TornadoLv 41 decade ago
First print these instructions off then go to Excel and do it in a blank sheet so you know what I'm talking about (you have to trick Excel).
Enter your first number in A1, enter your second number in B1.
Enter this formula in C1 to add your numbers:
=sum(A1:B1)
Enter this formula in D1 (we are retrieving the first number here).
=C1/60
Enter this formula in E1 (remove the number behind the decimal)
=rounddown(D1,0)
Enter this formula in F1 (retrieve the seconds)
=(D1-E1)*60
Now we hide two columns so we don't confuse the heck out of people...
Highlight column headers C & D (click on the C at the top and drag to D). Select Format, Column, Hide Columns.
Click on the E column header. Now we are going to format the first number so it has a colon behind it. Select Format, Cells and select the Number tab. Click on custom. Where it says Type type in 0: (that's a zero). Click OK.
Click on the F column header. Now we are going to format the second number so that numbers less than 10 have a zero in front of them. Select Format, Cells and select the Number tab. Click on custom. Where it says "Type" type in 00 (two zeros). Click OK.
Double click on the line between the F and G header to reduce the column width.
Cheers.
- 1 decade ago
Create 2 columns, right of the table. One is for hours, other is for minutes.
Hours column, write formula
=hour("select time cell at the left")
Minutes column, write formula
=minute("select same time cell at the left")
Then copy this formula till end of the table.
And finally calculate totals of hours and minutes.
- 1 decade ago
try this
=HOUR(A1)+(MINUTE(A1)/60)+(SECOND(A1)/360)
assuming A1 is where your time is and you have seconds in the time as well
