Formula in excel to calculate total time?

Answer 1

If you put in the time they started and the time they finished, you can subtract the earlier time from the later time. The time must be put in in a time format, with a colon between the hours and minutes of the time. So if they started at 9:30am you'd put it into a cell like this:

09:30

You would put the time they finish in another cell. Then in another cell you put a formula to subtract the earlier time from the later time. So if you had 09:30 in cell A3 and 15:45 in cell A4, then in cell A5 you could have a formula like this:

=A4-A3

If they have breaks, you'd have to have several formulas to find the time worked between each start point and end point. All those times could then be added to find out their total time.

A good way to calculate breaks in Excel using only one formula can be found in the training section of http://www.calculatehours.com:

Log in- - - - -using an hh:mm format in cell A1

Log out - - - using an hh:mm format in cell B1

Log in- - - - -using an hh:mm format in cell C1

Log out - - - using an hh:mm format in cell D1

Than in cell E1 type:

=SUM((B1-A1)+(E1-D1)*24

Change the cell format of E1 to "Number" with 2 decimal places.

If that's too difficult, you can download ready Excel Time sheet Templates from:

<a href="http://www.calculatehours.com/timesheet-template-library.html">

Answer 2

You first type in times as you want them, like this:

10:15

12:30

08:45

You can use the SUM function to add them, as you would for numbers. Totals will initially be done on a 24 hour basis, so if you added 5 hours to 8pm, you would get 1am for example. Obviously in some instances you want totals of time, so in the previous example you would want 25 hours. To do that, it is a matter of changing the formatting. Press Ctrl - 1 to open the Format Cells dialog. Go to Custom at the very bottom. Choose the [h]:mm:ss format. This will give you an actual total, not as a time. You can remove the :ss, which is for seconds, if you want just hours and minutes, so that it is like this: [h]:mm

So if you were to take the three amounts given above and put them into cells A2, A3 and A4, then you could use the following formula which would give 31:30 as the result:

=SUM(A2:A4)