Calculate total hours automatically deducting lunch breaks

Hi all,

I have a dillemma here,

I have to create a schedule which incorporates lunches automatically (either half hour or 1-hour lunches, depending on the circumstances).

The schedule only has time in and time out, but I need excel to automatically deduct the lunch break depending on the circumstance:

If you worked less than 6 hours = no lunch
If you worked more than 6 hours, but less than or equal to 8 hours = 0.5 hour lunch
If you worked more than 8 hours = 1 hour lunch

I am assuming the formula for this will be really long, but I have looked online everywhere and have not found ONE formula for it. I can't put lunch breaks seperately, so all I have to work with is Time in/out.

Also, I wouldn't be able to use military time, so I don't know how excel can assume that time in is in the morning, and time out is in teh evening and/or half-day.

If someone can help it would be greatlyyyy appreciated

It's easier if you can input times in a format excel can understand so for 9 to 5 either

9:00 AM in A2 and 5:00 PM in B2 or 09:00 and 17:00

Then you can use this formula for total hours

=(B2-A2)*24-IF((B2-A2)*24>8,1,IF((B2-A2)*24>6,0.5,0))

format result cell as number

THANK YOU DADDYLONGLEGS!!!

This is EXCELLENT!

is there any way excel can automatically recognize the first time in as AM, and the second as PM?

9:00
5:00

?

YES, but you have to use military time, ie, format cell to time (am/pm) and use 9 for am and 17 for pm (5pm)

Hello,
I have a question similar to the one above. I have the total time, but I need to add 30 minutes to it if they worked over 8hours. this is what I have below.
in my cell B3 I have 12:21 so in my B4 cell my formula is =B3+TIME(0,30,0) Which gives me 12:51 that is correct, but if they didn't work 8 hours, I don't want it to add the 30 min.
I thought that it could be =if(b3>8,b3+time(0,30,0)) but that isn't working. any suggestions?

Thanks,
Aseneth

Thank you for this, its been a great help.

Can you help a little further with my predicament? I'm looking to create a rota, this has helped calculate hours minus lunch breaks however I also need to add a an additional routine on how to remove/discount/not include additional text like sick/maternity/holiday etc. so that the total hours calculated do not include text for miscellaneous information and count it as zero.

please can you help with this?

kind regards billy