Calculating Time

V

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

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.
 
V

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

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)
 
S

Sandi

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


T. Valko said:
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)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Timesheet - Formula 4
Time Sheet - Calculating Time Differences for Totals 3
IF validation for time totaling 2
Excel Formula 2
TIME SHEETS 1
formula help 3
Daily Timesheet 5
Overtime Calculations 4

Top