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.

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.

Anyone have any ideas how to manage this? Thanks in
advance.......Herb

Herb,

multiply by 3, that is

=SUM(A1:A5)*3

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Herb" wrote in message
...

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.

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.

Anyone have any ideas how to manage this? Thanks in
advance.......Herb

How about this

=INT(SUM(A1:A5)*3)+(SUM(A1:A5)*3-INT(SUM(A1:A5)*3))/3

Custom formated as dd hh:mm would give you 02 07:00

Just multiplying by 3 would give you 2.875 which is 2 days and 21
hours. The remaining fraction needs to be returned to its original by
dividing it by 3.

Jon


Bob Phillips wrote:

Herb,

multiply by 3, that is

=SUM(A1:A5)*3

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