Lunch is between 12 and 1 Monday to Friday.
No work on Saturday and Sunday.
(wish I had a job that worked those hours
Try this:
(Start time is in cell A1 and end time in cell A2.)
=DAY(A2)-DAY(A1)-2*(INT((DAY(A2)-DAY(A1))/7)+(WEEKDAY(A2)<
WEEKDAY(A1)))-(MOD(A2,1)<MOD(A1,1)) & " days and " & (TEXT(
IF(MOD(A2,1)>=MOD(A1,1),MOD(A2,1)-MOD(A1,1),8.5/24+(MOD(A2,1)-
MOD(A1,1)))-1/24*(OR(AND(MOD(A1,1)<0.5,MOD(A2,1)>0.5),AND(MOD(A2,1)<
MOD(A1,1),OR(MOD(A1,1)<=0.5,MOD(A2,1)>0.5)))), "h:mm"))& " hours:mins"
This formula is made under the assumption that no start or end of work
can take place during lunch hour or outside working hours, and that
the work starts before it ends
This is how it works:
The first part calculates the difference in day by subtracting 2 days
for each complete week. Also subtrackt 2 days if the end date is on an
earlier weekday than the starting date. Finally subtract one day if
the end time is earlier on the end day than the starting time on the
start day. This give the number of (whole) days.
The second part calculates the additional hours by subtracting the
start time within the start day from the end time within the end day.
Add 8.5 hours to get a positive value if the end time is earlier than
the start time.
Finally subtract 1 hour, i.e. 1/24 days if there is a lunch break in
the interval. That happens either if the start time is before noon
(0.5) and the end time if after noon or if the start time is after
the end time and either the start time is before noon or the end time
is after noon.
Lars-Åke