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!

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.

--
Biff
Microsoft Excel MVP


" wrote in
message ...
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!

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! :)

" wrote:

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!

I was wondering when you calculate time entries, do they need to be in
military time, or can they be in regular time? I need to have it calculate
the time as:
11:00 in
1:45 out
I there a formula for that?
--
Sandi


" wrote:

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!

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)

--
Biff
Microsoft Excel MVP


"Sandi" wrote in message
...
I was wondering when you calculate time entries, do they need to be in
military time, or can they be in regular time? I need to have it
calculate
the time as:
11:00 in
1:45 out
I there a formula for that?
--
Sandi


" wrote:

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!

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


"T. Valko" wrote:

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)

--
Biff
Microsoft Excel MVP


"Sandi" wrote in message
...
I was wondering when you calculate time entries, do they need to be in
military time, or can they be in regular time? I need to have it
calculate
the time as:
11:00 in
1:45 out
I there a formula for that?
--
Sandi


" wrote:

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!

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


"T. Valko" wrote:

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)

--
Biff
Microsoft Excel MVP


"Sandi" wrote in message
...
I was wondering when you calculate time entries, do they need to be in
military time, or can they be in regular time? I need to have it
calculate
the time as:
11:00 in
1:45 out
I there a formula for that?
--
Sandi


" wrote:

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!

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

--
Biff
Microsoft Excel MVP


"Sandi" wrote in message
...
Thank you, thank you T...I feel real stupid but it worked great!
--
Sandi


"T. Valko" wrote:

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)

--
Biff
Microsoft Excel MVP


"Sandi" wrote in message
...
I was wondering when you calculate time entries, do they need to be in
military time, or can they be in regular time? I need to have it
calculate
the time as:
11:00 in
1:45 out
I there a formula for that?
--
Sandi


" wrote:

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!