On Thu, 25 Dec 2003 14:10:34 -0800, "Herb"
wrote:


Hi, I'm a little puzzled about how to calculate time in
timetracking system. I want to report on the amount of
time spent on different projects.

1) I format a cell with [h]:mm, and enter 1:00, for one
hour spent on a project. The formula bar says the entry is
1:00:00 AM. This is not what I am expecting.


But that's what will happen if Excel is interpreting an entry as TIME. The
formatting will affect how the entry get's displayed in the cell.


2) I need to convert the total time spent into days. If I
have two cells with 8:00 hours and one with 7:00 hours, it
should be 2 days, 7 hours. With the formula =(A1:A5)/8 the
result shows 2:52.

Excel stores time as fractions of a 24 hour day. So the 8:00 hours is stored
as 0.3333333 and the 7:00 hours is stored as 0.29166666.

To have your sum display as the string "2 days, 7 hours" would require some
appropriate mathematical formulas. If your SUM is in A5, then:

=TEXT(INT(A5/TIME(8,,)),"0")&" days " &
TEXT(MOD(A5,TIME(8,,))*24,"0.0")&" hours"


--ron