Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Time Calculation For A Timesheet To Include Lunch
I have seen many postings for time calculation but nothing that
includes a factor for lunch. I am trying to create a Timesheet so I can calculate the number of hours worked per day, but have not been able to find a way to include the Lunch time in the formula and to have it displayed correctly. I have cells for Start Time and End Time formatted as HH:MM, plus Lunch formatted as Numeric. Example: Start 8:30AM, End 5:30PM, Lunch 1:00 A formula of End - Start (ie: D4-C4) will give me 9:00 (9 hours). How can I deduct the 1 hour for lunch? This is not a time, just a number of hours/minutes. I tried converting to all different values with no luck. Thanks in advance. Tony |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Time Calculation For A Timesheet To Include Lunch
Assume cell A1 has time in, B1 has time out, and C1 has the number of
hours (not minutes) for lunch. The formula would be: =HOUR(B1)-HOUR(A1)-C1 If you want to track lunch in hours and minutes, I think there's a minute function out there (did not test). HTH, Johnny |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Time Calculation For A Timesheet To Include Lunch
Thanks very much for your help - it looks as if many helpful people are
already responding to my request - Thanks to you all.... I did FINALLY manage to solve the problem as follows: =D4-C4-TIME(0,E4*60,0) C4 is my Start Time, D4 is my End Time, and E4 is the Lunch Time. I tried to deduct this as hours which was ok for whole hours, so I changed my TIME formula to multiply the Lunch by 60 (ie: 1.00 is 60 minutes, 0.50 is 30 minutes) and that worked. Such a simple task has to be so complicated.... Thanks again everyone. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Show timesheet time in and out in regular time versus military tim | Excel Worksheet Functions | |||
Please help with time calculation | Excel Discussion (Misc queries) | |||
The shortcut to enter time in excel should include seconds. | Excel Discussion (Misc queries) | |||
"Manual calculation" takes less time. Why? | Excel Discussion (Misc queries) | |||
Excel - Time calculation | Excel Discussion (Misc queries) |