how to calculate total hours

TommyB,

Format columns A, B and C as Duration. Since you appear to be using 12-Hr time with AM and PM, write in Column C: =IFERROR(IF(B-A<$B$14,B-A+$C$14,B-A), ".")

Set your table to have 1 Footer Row, and set the total number of rows so that this footer row is row 14. Fill the expression in C to the last body row, row 13.

Format A14 as 0h 0m. Format B14 and C14 as h:mm.

In A14 write: =SUM(C). In B14 and C14, write the constants 0h 0m and 12h 0m.

When entering data, do not use AM and PM. Just type the time in h:mm format.

This solution will cover the normal business day. If your schedule is other than that, please describe in detail.

You may hide column C if you wish. You may make the constants in the footer row invisible by setting the Opacity of the Text Color to 0%.

Regards,

Jerry

TB,

I believe that I gave you exactly what you are asking for except for the new requirement of reserving column C for description. You can Hide column C, or you can place the hours difference calculation in another column and adjust the formula in A14 accordingly.

The constants in the footer row, 0h 0m and 12h 0m, are necessary for the calculation of time per job. They could be placed elsewhere if you need B14 and C14 for something else.

Please be specific in your concerns about the suggestion that I offered so I can make adjustments to suit you.

Jerry

Hello

Jerrold gave you a perfect scheme for those accustomed to apply the format Duration to cells in which they insert Start or End time.

Here is an alternate one for those accustomed to use date_time objects which is the case for those grabbing values thru machines recording them.

As I often explained,Numbers hasn't time objects, only date_time ones. So, when we need to isolate the time component of a value store in a cell, we must use the TIMEVAL function which returns the time component in a decimal number of day (12 h = 0.5)

As long as start and end are in the same day, calculating the time elapsed between start and end time just requires a subtraction:

=TIMEVAL(end_time)-TIMEVAL(start_time)

As most of us aren't at ease when time values are displayed as decimal day, I convert the result in a duration object.

I also take care of cases where Start or/and End aren't already entered.

The result is the formula :

=IFERROR(DURATION(0,0,0,(TIMEVALUE(B)-TIMEVALUE(A))*24*60),"")

If you don't understand it at first reading, don't worry.

Re-read my description whith iWork Formulas and Functions User Guide open to the pages describing the used functions.

To work efficiently, I choose to calculate the sum of durations in a footer row.

My preferred choice is to put it in a header row because in this case, the reference to the sum doesn't change if we insert new rows but I know that many users dislike this choice.

So, in D15, the formula is a bare

=SUM(D) which sum the values stored in standard cells of the column.

Wopking with durations in this column is required because durations aren't restricted to the range 00:00:00 to 23:59:59.

As you saw, I took benefit of the fact that Numbers whose default format is 1h 02m 03s allow us to use the format 1:00:00

I know, it may be a bit annoying when the sum is greater than 24 h. if we let the app display 1:15:15 because we can't decide if it display 1 day 15 h 15 m or 1h 15m 15s.

To get rid of that, I took care to define the duration format so that it display only hours and minutes so we will not qet 1/15/15 but 39:15

Yvan KOENIG (VALLAURIS, France) mercredi 29 juin 2011 16:45:26

iMac 21”5, i7, 2.8 GHz, 4 Gbytes, 1 Tbytes, mac OS X 10.6.8

Please : Search for questions similar to your own before submitting them to the community

To be the AW6 successor, iWork MUST integrate a TRUE DB, not a list organizer !

To take a screen shot:

• Set up the screen to show what you want to copy.
• Place the mouse pointer at one corner of what you want to show.
• Press shift-command-4 (the mouse pointer will change to a cross and circle)
• Hold down the mouse button and drag a selection rectangle (shaded area) to contain what you want to show.
• Release the mouse button. The screen shot of the selected area will be saved to the Desktop with the name "Picture n" (where n is a number).

To insert the screen shot in a post:

Click the camera icon above the Compose box. (The Insert image dialogue will open.)

• Click Choose File. The Choose dialogue will drop down from the title bar.
• If the dialogue does not show the Desktop, click the Desktop icon to the left.
• If the files are no sorted by Date, click Date (top of the second column)
• Click your Picture file (it should now be the one at the top of the list), then click Choose.
• The dialogue will slide up, and the Insert Image dialogue will now show the file name where it said No file selected.
• Click Insert Image.
• Done. continue composing your post.

Regards,

Barry

I'm sorry to bother you but clearly you didn't copied carefully what I posted which behave flawlessly.

Look carefully at the screenshot

Tableau 1 is the one which I posted yesterdays.

Tableau 2 is treating your "break" problem.

As you may see, both behave flawlessly.

You may see the formulas which are used when the document is open on a French System.

The English ones are :

Tableau 1

in D2 :

=IFERROR(DURATION(0,0,0,(TIMEVALUE(B)-TIMEVALUE(A))*24*60),"")

in D3 :

=IFERROR(DURATION(0,0,0,(TIMEVALUE(B)-TIMEVALUE(A))*24*60),"")

in D4 :

=IFERROR(DURATION(0,0,0,(TIMEVALUE(B)-TIMEVALUE(A))*24*60),"")

I didn't filled the other cells of the column to keep the list of formulas short.

in D15 :

=SUM(D)

There are those which I posted yesterdays !

Tableau 2 taking care of the possible string "break" inserted in a cell of column C

in D2 :

=IFERROR((DURATION(0,0,0,(TIMEVALUE(B)-TIMEVALUE(A))*24*60))-DURATION(,,,IF(C="b reak",30,0)),"")

in D3 :

=IFERROR((DURATION(0,0,0,(TIMEVALUE(B)-TIMEVALUE(A))*24*60))-DURATION(,,,IF(C="b reak",30,0)),"")

in D4 :

=IFERROR((DURATION(0,0,0,(TIMEVALUE(B)-TIMEVALUE(A))*24*60))-DURATION(,,,IF(C="b reak",30,0)),"")

I didn't filled the other cells of the column to keep the list of formulas short.

in D15 :

=SUM(D)

I hope that this time you will enter carefully the given formulas.

Yvan KOENIG (VALLAURIS, France) jeudi 30 juin 2011 10:58:53

iMac 21”5, i7, 2.8 GHz, 4 Gbytes, 1 Tbytes, mac OS X 10.6.8

Please : Search for questions similar to your own before submitting them to the community

To be the AW6 successor, iWork MUST integrate a TRUE DB, not a list organizer !

Tommyboy29 wrote:

Good news, i found a way that will suit my needs i guess. column a and b as time/date. column c as duration.

cell a15 with the formula: =SUM(b2-a2)+(b3-a3)+(b4-a4) and so on. that gave me a total of hours in a15. then in column c i simply applied the formula: =SUM(B-A) for each row and in each row, column c it gave me a total hours.

Hi Tommy,

SUM isn't necessary in either of these formulas, and is, in fact, inefficient.

Here's an example using small numbers: Suppose B is 3 and A is 2.

=B-A is then evaluated as 3-2, and returns the value 1.

For =SUM(B-A), Numbers starts inside the brackets and evaluates 3-2, then passes the result (1) on to the SUM function, and evaluates SUM(1), then returns the result 1.

=B-A and =SUM(B-A) both give the same result, but it takes an extra step to get that result if the subtraction is nested in the SUM function.

In the first formula, SUM() applies only to the result of b2-a2, the only part of the formula that is enclosed in the brackets attached to SUM. Everything else in the formula uses the subtraction operator "-" to find the difference between the two terms inside each pair of brackets, then the addition operator "+" to add those results.

Since every term in the first formula (eg. b3-a3) is calculated in column C, the long formula can be replaced with =SUM(C), which sums all of the results in column C.

so in column c2 it said 3:45 because a2 was 7:00 and b2 was 10:45. c3 then gave me :15 because b2 was 10:45 and b3 was 11:00 which then dymanically updated a15 to 4 hours. so i went a slightly different route then you guys siggested but it worked out for me anyway. now if i could figure out the formula to automatically subtract break time based on the description in column d. conditional format rules didnt seem to give me any options to use.

"Conditional format rules" are used to format the background and text in a cell when the contents of the cell meet conditions set in the rules. They have no effect on the calculations done with the content of the cell. I've ignored the times above and used the altered set you gave below in the sample table.

if a2 is 7:00 and b2 is 10:30 giving me a total of 3.5 hours in c2. d2 describes the job the technician did. then a3 is 10:30 and b3 is 11:00 totaling 30 minutes in c3, in d3 he writes break. i want the spreadsheet to know that he went on break and subtract 30 minutes. so in the 4 hours that hes been working hes getting paid for 3.5. i thought the conditional format rules where kind of like the smart playlist rules in itunes but i could specifically type in a "text is" "break" rule and then a formula to subtract that amount. anyone have any ideas?

Here's a sample table using the values you provided in the paragraph above.

Row 1 in a Header row. Row 15 is a Footer Row.

Columns A and B are formatted as Date and Time, show only the Time. Column C is formatted as Duration.

Formulas: C2, and filled down to C14:

=IF(OR(B=0,A=0),"",B-A)

The core part of this formula is B-A, which calculates the difference between the times in columns B and A on that row. The core formula is nested in an IF statement that suppresses the calculation until both A and B have values entered. The purpose is to place either a text value (a null string) or a duration into C to avoid the error that would occur if some cells in C contained durations and some contained the number zero.

C15:

=SUM(C)

This is the sum of all the durations in column C, including the times labeled "break"

Placing the formula in a Footer row excludes the cell containing the formula from the sum.

A15:

=SUM(C)-SUMIF(D,"break",C)

The SUMIF part of this formula adds all of the durations marked "break" in column D, and subtracts the result from the sum of all the durations calculated by SUM(C).

RESTRICTION: Because the cells in columns A and B contain Date and Time values (even though they show only the Time part), it is essential that both times be entered on the same calendar day (assuming both times are before midnight). If you have shifts that cross the midnight boundary, you will need to either adjust the formulas to accommodate that, or ensure that all start and end times are entered on the date that each one occurs.

Regards,

Barry

Thanks Barry, your solution was elegant and concise. Helped me with exactly what I needed.