Hi,

Im trying to create a time-tracking sheet in Excel.

For each job, I have tcolumns showing the start time, finish time and
duration. I want to subtract the start time from the end time to get the
duration.

If I format the duration cell (in this case G12) as a number and then enter
the simple formula,

=F12-E12

(where F12 is end time and E12 is start time), then I get the value 0.

If I format the duration cells as time value, it executes the formula, but
then I have a further problem.

At the top of the worksheet is a table of days of the month, with a cell
showing the date of each day. Next to each days €œdate cell€� is a cell which
contains the following formula (with cell references updated to be relevant
to the particular row).

=SUMIF($A$12:$A$69,B5,$G$12:$G$69)

If the cells in the G column are formatted to show time values, this formula
cannot find and calculate the sum of the relevant values.

Can anyone suggest a way to make this work? Help very much appreciated.

Thanks

Karl

Karl,

With a date in B5 your formula works fine for me, the only thing I would
note is that the formula cell should be formatted as [hh]:mm

Mike

"Karl" wrote:

Hi,

Im trying to create a time-tracking sheet in Excel.

For each job, I have tcolumns showing the start time, finish time and
duration. I want to subtract the start time from the end time to get the
duration.

If I format the duration cell (in this case G12) as a number and then enter
the simple formula,

=F12-E12

(where F12 is end time and E12 is start time), then I get the value 0.

If I format the duration cells as time value, it executes the formula, but
then I have a further problem.

At the top of the worksheet is a table of days of the month, with a cell
showing the date of each day. Next to each days €œdate cell€� is a cell which
contains the following formula (with cell references updated to be relevant
to the particular row).

=SUMIF($A$12:$A$69,B5,$G$12:$G$69)

If the cells in the G column are formatted to show time values, this formula
cannot find and calculate the sum of the relevant values.

Can anyone suggest a way to make this work? Help very much appreciated.

Thanks

Karl