software

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 amp; 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 amp; Times page, covering an array of date and time arithmetic
functions.

www.cpearson.com/excel/datetime.htm
--
Kevin Backmannquot;Tracyquot; wrote:

gt; I am trying to create a spreadsheet that will calculate time cards less a
gt; lunch period. I cannot seem to get it to calculate the reduction of the
gt; lunch period properly. Columns A,B amp; C would be input. Can anyone help?
gt;
gt;
gt; Column A Column B Column C Column D
gt; Start time End time Lunch Total Hours
gt;
gt; 7:32 4:00 :30 ?
gt; 7:30 4:15 :30 ?
gt; 7:35 4:30 :35 ?
gt; 8:00 4:30 0 ?
gt; 7:30 4:02 :30 ?
gt;
gt; Total Hours ?
gt;
gt;
gt;
gt;
gt;

Thank you I will try the website.

quot;Kevin Bquot; wrote:

gt; This site has great information on a variety of Excel topics, this page link
gt; is to the Dates amp; Times page, covering an array of date and time arithmetic
gt; functions.
gt;
gt; www.cpearson.com/excel/datetime.htm
gt; --
gt; Kevin Backmann
gt;
gt;
gt; quot;Tracyquot; wrote:
gt;
gt; gt; I am trying to create a spreadsheet that will calculate time cards less a
gt; gt; lunch period. I cannot seem to get it to calculate the reduction of the
gt; gt; lunch period properly. Columns A,B amp; C would be input. Can anyone help?
gt; gt;
gt; gt;
gt; gt; Column A Column B Column C Column D
gt; gt; Start time End time Lunch Total Hours
gt; gt;
gt; gt; 7:32 4:00 :30 ?
gt; gt; 7:30 4:15 :30 ?
gt; gt; 7:35 4:30 :35 ?
gt; gt; 8:00 4:30 0 ?
gt; gt; 7:30 4:02 :30 ?
gt; gt;
gt; gt; Total Hours ?
gt; gt;
gt; gt;
gt; gt;
gt; gt;
gt; gt;

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(D26)
you will need to format it as
[h]:mm
otherwise it will show as
5:05 PM

Start timeEnd timeLunchTotal Hours
7:324:000:307:58
7:304:150:308:15
7:354:300:358:20
8:004:300:008:30
7:304:020:308:02

Total Hours41:05quot;Tracyquot; wrote:

gt; I am trying to create a spreadsheet that will calculate time cards less a
gt; lunch period. I cannot seem to get it to calculate the reduction of the
gt; lunch period properly. Columns A,B amp; C would be input. Can anyone help?
gt;
gt;
gt; Column A Column B Column C Column D
gt; Start time End time Lunch Total Hours
gt;
gt; 7:32 4:00 :30 ?
gt; 7:30 4:15 :30 ?
gt; 7:35 4:30 :35 ?
gt; 8:00 4:30 0 ?
gt; 7:30 4:02 :30 ?
gt;
gt; Total Hours ?
gt;
gt;
gt;
gt;
gt;


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-(0amp;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 0amp; to make excel recognize it. Even if the
lunch time is 1:00, it will work.

Does that help?

Steve--
SteveG
------------------------------------------------------------------------
SteveG's Profile: www.excelforum.com/member.php...foamp;userid=7571
View this thread: www.excelforum.com/showthread...hreadid=505465Yes, 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.

quot;SteveGquot; wrote:

gt;
gt; Tracy,
gt;
gt; Not sure if you got a solution but this worked for me. Not sure if
gt; it's the most efficient but it worked.
gt;
gt;
gt; =(B2 60/24)-A2-(0amp;C2)
gt;
gt; Where B2 is the End Time, A2 is the Start Time and C2 is the lunch
gt; time. One of the problems with your data is that :30 is not recognized
gt; as a time so I used the 0amp; to make excel recognize it. Even if the
gt; lunch time is 1:00, it will work.
gt;
gt; Does that help?
gt;
gt; Steve
gt;
gt;
gt; --
gt; SteveG
gt; ------------------------------------------------------------------------
gt; SteveG's Profile: www.excelforum.com/member.php...foamp;userid=7571
gt; View this thread: www.excelforum.com/showthread...hreadid=505465
gt;
gt;