Question: How can I add up elapsed times in a ClarisWorks spreadsheet to get total number of hours? When I do the addition, if the result is over 24 hours, I only get the remainder. For example, I want to add 3 times together: 7 hrs 15 min, 12 hrs 20 min, 8 hrs 15 min. When I add them together I get 3:50! What can I do?
Solution: Format the times so that they are in 24 hour format (7 hrs 15 min =7:15) by selecting the "number" item under the format menu. Use the "sum" function as you normally would but display the result as a serial number by selecting a format of "General" or use a format of "Fixed" with a precision of 6.
The resulting number is in "serial format." The portion of the number that is to the left of the decimal point is the number of 24 hour days that have elapsed. The number to the right of the decimal point is the number of minutes and seconds that have elapsed. You can then use the "Trunc" function to discard the fractional portion of this serial number and multiply by 24 to convert to elapsed hours. So if the resulting serial number was 2.115589, using the "Trunc" function & multiplying by 24 would give me: 2*24= 48 hrs.
Similarly, you can use the "Hour," "Minute," and "Frac" functions to extract number of hours and minutes from the decimal portion of the serial number. For the example of the 3 numbers above, I would get a serial number result of 1.159722. Then I could perform the following calculations:
Hours1= Trunc (1.159722) * 24
Hours2= Hour(1.159722)
Minutes=Minute(1.159722)
Adding Hours1 and Hours2 would give me the total number of hours elapsed. The following formula displays the total number of hours and minutes in 0:00 format.
=(TRUNC(1.159722)*24)+(HOUR(1.159722))&":"&RIGHT("00"&MINUTE(1.159722),2)