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 ?

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

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

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

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

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