Hi!
While it is possible to calculate if you have a cell entry
like: 9-5:30, it is really complicated and not
recommended. You would be better served by splitting the
times into 2 cells: Time In, Time Out.
Here's one way to calculate total time and account for a
30 min break if hours are over 7.5:
B1 - Time In = 9:00 AM
C1 - Time Out = 5:30 PM
=IF(C1-B17.5/24,C1-B1-30/1440,C1-B1)
How do you want these results to be displayed?
As a decimal - 8
As a time - 8:00
If you want a decimal value result, use this formula:
=IF(C1-B17.5/24,C1-B1-30/1440,C1-B1)*24
Format the cell as GENERAL.
If you want a time: 8:00, just use the first formula and
format the cell as h:mm.
One other thing to consider is, will times worked span
past midnight?
Will someone work from, say, 11:00 PM to 7:00 AM?
Biff
-----Original Message-----
my excell spreadsheet looks like this
colum A 20 peoples names
colum B-H days of week
colum I will calculate total hours worked for employees
with a grand total at the bottom of I
need to calculate hours worked totals without 30 min
lunch breaks if hours
worked is over 7.5 hours
so cell B2 would say 9-5:30
need cell B3 to say 8 (as in 8 hours worked with 30 min
break)
but part of the problem is that a person can work 7 hours
without a lunch
and get paid so we cant take the 30 min break out unless
empleyee is over 7.5
hours
the real answer I need in the end is total hours without
breaks for full
time and part time people just cant figure out the
formula that eliminates
that half hour and a formula that reads 2 times in 1
cell (9-5:30) if I have
to break that part into 2 cells ....so be it... IF ANYONE
CAN HELP that would
be great ....been trying to work on it for 3 days
.
|