Hi 'dad,
"I can only get the "Total Hours" column (D18) to correctly total up hours worked if ALL the rows above it (D2-D17) contain time values in them."
Actually, NONE of the cells in column D will contain "time values." Nor, for that matter, will the cells in columns B and C ever contain "time values". Cells in B and C will either be empty, or will contain a Date and Time value. You may format these cells to display only the "time part" of a Date and Time value, but the date part and the time part are always both included in the value in that cell.
Cells in column D contain the results of a subtraction (C-B). The type of value depends on what type of values are in C and B.
- If both C and B contain Date and Time values, the result will be a Duration.
- If B contains a Date and Time value, and C is empty (or contains a number), the result will be an error message (see below).
- If C contains a Date and Time value and B is empty or contains a number, the result will be a Date and Time value as many days (and fraction of a day) earlier than the Date and Time value in C.
- If both C and B contain numbers or both are empty, or one contains a number and the other is empty, the result will be a number.
Here are four examples, using data from the first four rows of your table, All cell formats are "Automatic":
The error message you got (different from the one shown here) explains why the SUM formula is generating an error message—all of the values in column D are durations except the value in D6, which is a number. Ian's solution inserts a null string into column D in any row where nothing has been entered into either or both of columns B and C. Even though a null string is 'empty', it is read as a text value, and is ignored by SUM. All of the cells that SUM is paying attention to then contain duration values, and will SUM correctly.
Regarding your second issue: Conversion of duration to a number representing hours (and fractions of hours requires only enclosing the formula in column D in a DUR2HOURS statement. Here is the same table as above, with the revised formula. Don't be concerned that three cells now have error triangles, where only one had an error before. The first is the same error message as shown above, the second is shown below, and the third is similar to the second (but says it found 0).
Ian's test for empty cells will also work here:
One thing that puzzles me regarding your original table is the correct results in rows 11 through 17, where work begins on one (calendar) day and ends on the next calendar day. In row 11, for example In is 13:00 and Out is 0:15. If both these values were entered on the same calendar day, the date part of both Date and Time values would be the same, and the expected result would be -12:45 (for C-B) or -12.45 (for DUR2HOURS(C-B). To get the correct result (11:15 or 11.25) would require entering B on one day and C on the next (calendar) day, or would require a formula that would make the correction when needed.
It's also possible that Numbers for iOS has the built in smarts to make this adjustment where the data suggests that the work period crosses the midnight boundary.
Regards,
Barry
