Calculating hours w/ ½ hour break

hey excel noob here... thanks beforehand for any help you can give

I'm trying to calculate hours worked while taking into consideration a half hour break if worked over 6 hours and a 1 hour break if worked over 8 hours.

I've come up with 2 functions that kind of work...

The first one works and automatically takes out the ½ hour break and 1 break, but it only works if your day starts and ends in the same day.

Example... A1 being start time, B1 being end time, C1 being total hours

A1 - 9:00 AM B1 - 5:00 PM C1 - 7.5

for C1 the function is =(B1-A1)*24-IF((B1-A1)*24>8,1,IF((B1-A1)*24>6,0.5,0))

Now where this function starts to go wrong is when you start at night and go into the morning.

using the same function and a new scenario, I get

A1 - 9:00 PM B1 - 4:00 AM C1 - -17.0

what should be 6.5 hours worked is coming up as -17.

Any help on why it's doing that and how I could fix it?

Now I have a second function that works with both scenarios but it doesn't take out a break...

A1 - 9:00 PM B1 - 4:00 AM C1 - 7:00

the function in C1 is =IF(B1<A1,B1+1,B1)-A1

Any help on getting a break into that function? Again thanks for any help you can give me, I just started messing around with excel and google has helped me with mostly everything I've needed so far but i've come to a roadblock with this particular function.

Try this:
=IF(MOD(B2-A2,1)>8/24,MOD(B2-A2,1)-1/24,MOD(B2-A2,1)-0.5/24)

Hm, one problem with it though is if you work 4 hours it still takes out a ½ hour break.

is there a way to only take out the break between 6-8?

Originally Posted by DonkeyOte

Perhaps - assuming you want a decimal hours return:

=24*MOD(B1-A1,1)-LOOKUP(24*MOD(B1-A1,1),{0,6,8},{0,0.5,1})

If you prefer a Time output it can be adjusted quite easily - let us know.

That works perfectly, thanks a ton.

Didn't see DonkeyOte reply which is way better!