Sunshine said:
I have learned to add time by format cells/numbers/customs. Using the
format hh:mm:ss I can add time and get a sum that I can understand, for
example 40:00:00. If I use format [h]: mm:ss I get a sum of 960:00:00,
which I don't understand nor do I know how to convert. I just change the
format to numbers. When I calculate 960:00:00*$10=$400. When I calculate
40:00:00*$10=$16.67, which I don't understand nor can I convert and
switching between the two time formats does not convert it . The ideal
results would read 40:00:00*$10=400. Now, when I add 08:12:58 + 08:10:37 =
16:23:35. When I add 192:12:58 + 192:12:58 =16:02. Why are the totals
different? I cannot create a worksheet formating the cells as numbers total
the same as formatting the cell using the custom format for time. Please
help!!"
First - format can't change the underlying values, so calculations
*must* work the same regardless of how the input cell is formatted.
Second, XL stores times as fractional days, so 12:00:00 = 0.5, 8:00:00 =
0.333333333333333, etc. The format h:mm:ss only displays the fractional
part of the number - i.e., it rolls over every 24 hours. Putting the
brackets around [h] keeps the rollover from happening.
If you've formatted the cell as [h]:mm:ss and you get a result of
960:00:00 it's because the underlying value in the cell is the integer
40.
I have no idea how you'd get 40:00:00 in a cell formatted as h:mm:ss -
it should be 16:00:00 since the format without the brackets tells XL to
roll time over every 24 hours.
Perhaps you have the cell formatted as 00":00:00" (i.e., display
integers, followed by the string ":00:00")???
Now, since times are stored as fractional days, you need to multiply
them by 24 hours/day when you calculate wages:
A1: 08:12:58
A2: 08:10:37
A3: =SUM(A1:A2) ==> 16:23:35
A5: 10
A6: =A3*24 * A5 ==> 163.9305556
when A6 is formatted as general. Or, better:
A6: =ROUND(A3*24*A5,2) ==> 163.93
