When dealing with time it is usually better to input it as h:mm AM/PM. This
will prevent any confusion. For instance 4:00-7:32 equals -3:32, which has
no meaning as far as excel is concerned. If you know that the 2nd time is
always PM then you can account for it by adding 0.5
=0.5+B2-A2-C2
the 0.5 adds 12 hours. So what this is actually adding is
=12:00+4:00 AM-7:32 AM-0:30 AM
which equals 7:58 AM.

Also the lunch times need to be entered as times (:30 has no meaning to
excel). You can change the format to :[mm] to show the way you want. But
you have to enter it as 0:30, or 12:30 AM.

Here is a table with your sample data. I did NOT change the end time to PM.
If you do, then delete the 0.5 in the formula.

The daily total hours uses this formula
=0.5+B2-A2-C2
and the Weekly total hours uses this formula
=SUM(D2:D6)
you will need to format it as
[h]:mm
otherwise it will show as
5:05 PM

Start time End time Lunch Total Hours
7:32 4:00 0:30 7:58
7:30 4:15 0:30 8:15
7:35 4:30 0:35 8:20
8:00 4:30 0:00 8:30
7:30 4:02 0:30 8:02

Total Hours 41:05


"Tracy" wrote:

I am trying to create a spreadsheet that will calculate time cards less a
lunch period. I cannot seem to get it to calculate the reduction of the
lunch period properly. Columns A,B & C would be input. Can anyone help?


Column A Column B Column C Column D
Start time End time Lunch Total Hours

7:32 4:00 :30 ?
7:30 4:15 :30 ?
7:35 4:30 :35 ?
8:00 4:30 0 ?
7:30 4:02 :30 ?

Total Hours ?