# Calculate total hours automatically deducting lunch breaks

1. ## 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   Register To Reply

2. ## Re: Calculate total hours automatically deducting lunch breaks

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  Register To Reply

3. ## Re: Calculate total hours automatically deducting lunch breaks

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

?  Register To Reply

4. ## Re: Calculate total hours automatically deducting lunch breaks

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)  Register To Reply

5. ## Re: Calculate total hours automatically deducting lunch breaks

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  Register To Reply

6. ## Re: Calculate total hours automatically deducting lunch breaks

Asking another question in some others thread is not according to the forum rules  Register To Reply

7. ## Re: Calculate total hours automatically deducting lunch breaks

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  Register To Reply