Touche' Roger! Thank you for your insight but now a twist.. using the simple
formula of (c5-b5) then the formula with (time) is there a way to have it not
subtract if the total for that day is less than 8 hours? So if someone works
5 hours that day it will not calculate or subtract an hour for lunch?
"Roger Govier" wrote:
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" wrote in message
...
If I was to do a formula like (c5-b5)+(e5-d5) how can I subtract 2
from this
and still work?
"Roger Govier" wrote:
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 FormatCellsNumberCustom[h]:mm in order to sum
past 24 hours.
--
Regards
Roger Govier
"RJ Swain" <RJ wrote in message
...
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