Calculating Time

[Vicky]

I am trying to create a time sheet with the following columns: Time In, Time
Out, Lunch and Hours Worked. The Time In and Time Out columns are formatted
with military time and the Lunch is a straight time number as the time may
vary. I needed to have a formula that calculated the time worked (end time
minus start time minus the time taken for lunch.) I have created the
following formula to calculate the hours worked:
=IF(B15=0,"",((C15-INT(C15))-(B15-INT(B15)))-(D15-INT(D15)))

This formula functions properly, giving me the total hours worked for each
day. However, my problem is then summing the hours worked for the entire
week. I'm given an incorrect number when I use a standard =sum(E1:E5)
formula. I have tried various other formulas and cannot seem to find a way
to get the sum of the results from the above formula.

Is there anyone that can help?! Please!?

With much thanks!

 

[T. Valko]

Try formatting the result of your sum formula as [h]:mm.

The brackets keep the hours from rolling over at 24.

For example, formatted as h:mm

If the sum is 23:00 then this displays properly.

If the sum was 25:00 this would display as 1:00.

[h]:mm the brackets keep that from happening.

 

[Vicky]

Never mind. Figured it out... Should have kept reading - thanks Bob Phillips
for the answer in a previous post.

Answer is: use the regular sum formula, but then format the cell: [h].mm
That worked!

 

[T. Valko]

You can use a regular 12 hour clock *but* you have to include the AM/PM. If
you use a 12 hour clock and don't include the AM/PM Excel will default to
AM. For example:

A1 = 6:00

You might intend for that to mean 6:00 PM but since you didn't include the
PM Excel will treat it as 6:00 AM.

So:

A1 = 11:00 AM
A2 = 1:45 PM

Then:

=A2-A1

If the times might span past midnight then it's a little different:

A1 = 11:00 PM
A2 = 1:45 AM

Then:

=MOD(A2-A1,1)

 

[Sandi]

Still coming up with a value error..
--
Sandi

You can use a regular 12 hour clock *but* you have to include the AM/PM. If
you use a 12 hour clock and don't include the AM/PM Excel will default to
AM. For example:

A1 = 6:00

You might intend for that to mean 6:00 PM but since you didn't include the
PM Excel will treat it as 6:00 AM.

So:

A1 = 11:00 AM
A2 = 1:45 PM

Then:

=A2-A1

If the times might span past midnight then it's a little different:

A1 = 11:00 PM
A2 = 1:45 AM

Then:

=MOD(A2-A1,1)

 

[Sandi]

Thank you, thank you T...I feel real stupid but it worked great!

 

[T. Valko]

Ok, glad you got it straightened out. Thanks for the feedback!