How do I edit this formula to add up to 8 instead of 7 where it is "Hours Worked"?
=IF(C13=0,0,IF(F13=0,0,((+F13-C13)-(E13-D13))*24))
I have attached the timesheet I am trying to edit
This formula is the final total cell in a row of cells where the time is entered.
ie "IN"(start of work day) "OUT"(for lunch) "IN"(from lunch) "OUT"(end of work day) "Hours Worked"
Currently this formula subtracts the lunch time from the total hours worked.
Last edited by johnlyons; 01-08-2009 at 04:56 PM. Reason: found another problem with result
If you mean you wish to include lunch in hours worked:
=IF(OR(N(C13)=0,N(F13)=0),0,(F13-C13)*24)
(changed at 17:00 UK to mirror existing check of 0 values)
Last edited by DonkeyOte; 01-08-2009 at 01:00 PM.
My Recommended Reading:
Volatility
Sumproduct & Arrays
Pivot Intro
Email from XL - VBA & Outlook VBA
Function Dictionary & Function Translations
Dynamic Named Ranges
That was perfect. I knew I could count on the forum for the answer. Bless you>
I just found a problem with the calculation.
=IF(OR(N(C25)=0,N(F25)=0),0,(F25-C25)*24)
If a person signs "IN" at 10:00 PM and "out" at 2:00 AM the result is a negative number. How do we fix that?
Try this:
Does that help?
Please Login or Register to view this content.
Hi Ron,
That formula is 12 hours too high.
ie: 10:00 PM to 1:00 AM, the result of this formula is 15, when it should be 3
are you sure you entered 10 PM and not 10 (as in AM ?)
the formula should work without issue.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks