Hi
I am trying to calculate the hours worked in a day with or without a lunch break
so i have
A1 | B1 | C1 | D1
time in | lunch start | lunch End | time out
I have the formula
=SUM(IF(B1<A1,B1+1,B1)-A1,If(D1<C1,D1+1,D1)-C1)
so i can calculate the time, when there is a break added, but i need to be able to calculate the time, when there are no values in B1 AND C1 and when there is just a value in B1 it calculates the total time with out a break, and the same for C1
Do you mean there would be a start and end date but only one of the lunch times, either start or end?Originally Posted by kobiashi
i.e. only 3 of the 4 filled out?
BSB
Yeh
so if B1 and C1 are filled in then calculate the break time,
but if only B1 or C1 is filled in dont calculate the break, the reason i ask this, is i have another formula which will work out the time with or without the break added, but if you add a time to just B1 or C1 then the total time is completely wrong
Perhaps this will do what you need?
Formula:
Please Login or Register to view this content.
BSB
there is no need to cross check breaktimes against start and end times
start and endtime determine the total duration. you might want to check if end time is smaller if company has nightshifts
and start break and end break will cause a deduction. you can test the endbreak against startbreak but with a normal 30 min break chance is very small it will occor.
the simplest formula
=D1-A1-if(COUNT(B1:C1)=2;C1-B1;"")
if your company has shifts that go past midnight then you need the "smaller than" tests but you only need to test workday and break times against eachother
If(D1<A1;D1+1;D1)-A1-if(COUNT(B1:c1)=2;if(B1<C1;C1+1;C1)-B1;"")
Last edited by Roel Jongman; 06-10-2018 at 05:22 AM.
worked a treat thank you
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks