Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 ? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 ? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 ? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 ? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do you calculate payroll with tips? | Excel Discussion (Misc queries) | |||
how do i convert payroll calculator in U K format | Excel Worksheet Functions | |||
How do I keep each week's data from the payroll calcualtor? | Excel Worksheet Functions | |||
payroll calculator for ontario canada, cant convert ontario rates. | Excel Discussion (Misc queries) | |||
payroll calculator for ontario canada, cant convert ontario rates. | Excel Discussion (Misc queries) |