calculate hours just can't figure it out

[Guest]

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

 

[Biff]

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-B1>7.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-B1>7.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