Hi
There was a mistake in my formula, it should have been
=MOD(C1-B1,1)-(TIME(1,0,0)*COUNT(B1:C1)=2)
However, if you do not have times spanning 24 hours, then c5-b5 etc
will
work OK.
=(C5-B5)+(E5-D5)+(G5-F5)+(I5-H5)+(K5-J5)-
(TIME(1,0,0)*(COUNT(B5:K5)/2))
counting the values entered between B5 and K5 and dividing by 2 will
give the number of days worked which multiplied by TIME(1,0,0) will
give
the number of hours to be subtracted.
I might be inclined to multiply the whole thing by
(ISEVEN(COUNT(B5:K5))
which will give a result of 0 if there aren't matching pairs of times
for each day.
If you had 17:00 entered in G5 and nothing in F5 it would count 17
hours, and subtract 0.5 hour for lunch.
--
Regards
Roger Govier
RJ Swain said:
If I was to do a formula like (c5-b5)+(e5-d5) how can I subtract 2
from this
and still work?
:
Hi
To get the calculation for Monday
=MOD(C1-B,1)-(TIME(1,0,0)*COUNT(B1:C1)=2)
Using MOD() rather than just subtracting B1 from C1 will deal with
any
occurrences of times running over from 1 day to the next.
Deducting TIME(1,0,0) is being multiplied by the text to ensure
you
have
both start and end times, so the formula does not deduct an hour
for
any
days not worked.
Repeat procedure for other days and sum results.
The cell containing the formula that sums your intermediate
calculations
would need to use Format>Cells>Number>Custom>[h]:mm in order to
sum
past 24 hours.
--
Regards
Roger Govier
I am putting together a simple excel spreadsheet to calculate
hourly
schedules for my employees. What I would like it to do is
calculate
the hours
of the week and subtract 1 hour per day for lunch hours. So it
is
setup as
follows
A1 - Name
B1 - Start
C1 - Finish (Monday) (-1 hour lunch)
D1 - Start
E1 - Finish (Tuesday) (-1 hour lunch)
etc..
N1 - Total hours for the week