time

  • Thread starter Thread starter Herb
  • Start date Start date
H

Herb

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)
 
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
 
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.
Click to expand...

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.
Click to expand...

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
 
Back
Top