Payroll
 

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 ?

Payroll
 

This site has great information on a variety of Excel topics, this page link
is to the Dates & Times page, covering an array of date and time arithmetic
functions.

http://www.cpearson.com/excel/datetime.htm
--
Kevin Backmann


"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 ?

Payroll
 

Thank you I will try the website.

"Kevin B" wrote:

This site has great information on a variety of Excel topics, this page link
is to the Dates & Times page, covering an array of date and time arithmetic
functions.

http://www.cpearson.com/excel/datetime.htm
--
Kevin Backmann


"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 ?

Payroll
 

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 ?

Payroll
 

Tracy,

Not sure if you got a solution but this worked for me. Not sure if
it's the most efficient but it worked.


=(B2+60/24)-A2-(0&C2)

Where B2 is the End Time, A2 is the Start Time and C2 is the lunch
time. One of the problems with your data is that :30 is not recognized
as a time so I used the 0& to make excel recognize it. Even if the
lunch time is 1:00, it will work.

Does that help?

Steve


--
SteveG
------------------------------------------------------------------------
SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571
View this thread: http://www.excelforum.com/showthread...hreadid=505465

Payroll
 

Yes, you may also want to look at the reply from Kevin B. It's a website
with a lot of information on time issues and has some template downlaods.
Thanks for your help.

"SteveG" wrote:


Tracy,

Not sure if you got a solution but this worked for me. Not sure if
it's the most efficient but it worked.


=(B2+60/24)-A2-(0&C2)

Where B2 is the End Time, A2 is the Start Time and C2 is the lunch
time. One of the problems with your data is that :30 is not recognized
as a time so I used the 0& to make excel recognize it. Even if the
lunch time is 1:00, it will work.

Does that help?

Steve


--
SteveG
------------------------------------------------------------------------
SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571
View this thread: http://www.excelforum.com/showthread...hreadid=505465