Calculating/displaying elapsed time TOTALS

[USAOz]

I need some help resolving a calculation formula and/or display in Excel 2003.

I need to keep track of actual time worked on a project for variuos clients
and total that time.

I enter the start time in column B (display format is hh:mm:ss) and the
finish time in Column C (display format is hh:mm:ss).

Depending in a code entered in column A, the elapsed time is displayed in
either column D, E or F.

I calculate the elapsed time using the formula =(($Cn-$bn)+($Cn<$Bn)) where
n is the relevant row. The result is correctly calculated and is displayed
in the format hh:mm:ss

I wanted to be able to calculate the total time for each Category (Columns
D, E, and F) but using the =SUM($dn:$dx) where n is the starting row and x is
the ending row.

Here is where I come unstuck as the total hours simply do NOT add correctly
if the total hours exceed 24!

I also wanted to sum the totals of Columns D, E, and F but encounter the
same problem.

Can anyone suggest a solution to my problem?

Thanks very much (in anticipation).


D. Bruce
USAOz Services

 

[T. Valko]

Format the cells with the formulas as [h]:mm.

The brackets [ ] keep the hours from rolling over into days.

For example:

A1 = 20:00 (h:mm)
A2 = 5:00 (h:mm)

=SUM(A1:A2) = 1:00

This is because at 24:00 hours the total time rolls over into a day. If you
formatted that cell as GENERAL you'd see the true value is 1.041667 which is
the decimal equivalent of 1 day 1 hour or 25:00.

With the formula cell formatted as [h]:mm the result is 25:00.

 

[USAOz]

Thank you VERY much for an accurate and speedy response! You're a LEGEND!

Format the cells with the formulas as [h]:mm.

The brackets [ ] keep the hours from rolling over into days.

For example:

A1 = 20:00 (h:mm)
A2 = 5:00 (h:mm)

=SUM(A1:A2) = 1:00

This is because at 24:00 hours the total time rolls over into a day. If you
formatted that cell as GENERAL you'd see the true value is 1.041667 which is
the decimal equivalent of 1 day 1 hour or 25:00.

With the formula cell formatted as [h]:mm the result is 25:00.

--
Biff
Microsoft Excel MVP

I need some help resolving a calculation formula and/or display in Excel
2003.

I need to keep track of actual time worked on a project for variuos
clients
and total that time.

I enter the start time in column B (display format is hh:mm:ss) and the
finish time in Column C (display format is hh:mm:ss).

Depending in a code entered in column A, the elapsed time is displayed in
either column D, E or F.

I calculate the elapsed time using the formula =(($Cn-$bn)+($Cn<$Bn))
where
n is the relevant row. The result is correctly calculated and is
displayed
in the format hh:mm:ss

I wanted to be able to calculate the total time for each Category (Columns
D, E, and F) but using the =SUM($dn:$dx) where n is the starting row and x
is
the ending row.

Here is where I come unstuck as the total hours simply do NOT add
correctly
if the total hours exceed 24!

I also wanted to sum the totals of Columns D, E, and F but encounter the
same problem.

Can anyone suggest a solution to my problem?

Thanks very much (in anticipation).


D. Bruce
USAOz Services

 

[T. Valko]

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP

Thank you VERY much for an accurate and speedy response! You're a LEGEND!

Format the cells with the formulas as [h]:mm.

The brackets [ ] keep the hours from rolling over into days.

For example:

A1 = 20:00 (h:mm)
A2 = 5:00 (h:mm)

=SUM(A1:A2) = 1:00

This is because at 24:00 hours the total time rolls over into a day. If
you
formatted that cell as GENERAL you'd see the true value is 1.041667 which
is
the decimal equivalent of 1 day 1 hour or 25:00.

With the formula cell formatted as [h]:mm the result is 25:00.

--
Biff
Microsoft Excel MVP

I need some help resolving a calculation formula and/or display in Excel
2003.

I need to keep track of actual time worked on a project for variuos
clients
and total that time.

I enter the start time in column B (display format is hh:mm:ss) and the
finish time in Column C (display format is hh:mm:ss).

Depending in a code entered in column A, the elapsed time is displayed
in
either column D, E or F.

I calculate the elapsed time using the formula =(($Cn-$bn)+($Cn<$Bn))
where
n is the relevant row. The result is correctly calculated and is
displayed
in the format hh:mm:ss

I wanted to be able to calculate the total time for each Category
(Columns
D, E, and F) but using the =SUM($dn:$dx) where n is the starting row
and x
is
the ending row.

Here is where I come unstuck as the total hours simply do NOT add
correctly
if the total hours exceed 24!

I also wanted to sum the totals of Columns D, E, and F but encounter
the
same problem.

Can anyone suggest a solution to my problem?

Thanks very much (in anticipation).


D. Bruce
USAOz Services