# Calculating Time at work minus time at lunch

#### mdobber77

##### New Member
I am a manager. I like to keep my own time records for reference. Just in case. I have a straight time worked. example 8:00 to 16:30. I have to take an half hour for lunch off my time each day. How do i set this up in excel. I can get the hours worked + my time at lunch to total but how do i get the 30 minutes out? Please help

### Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
mdobber77 said:
I am a manager. I like to keep my own time records for reference. Just in case. I have a straight time worked. example 8:00 to 16:30. I have to take an half hour for lunch off my time each day. How do i set this up in excel. I can get the hours worked + my time at lunch to total but how do i get the 30 minutes out? Please help

Excel stores time as fractions of a day....1 being a day...1/24 an hour...1/48 a half hour.

Mdobber77,

Is your lunch break with a fixed time like 12:00 TO 1:00 PM or after you completed 4 hrs work?

Hours worked.....

Try this

=SUM(TEXT((C2-B2)-(E2-D2),"[h]:mm")+TEXT((G2-F2)-(I2-H2),"[h]:mm")+(TEXT((K2-J2)-(M2-L2),"[h]:mm")+TEXT((O2-N2)-(Q2-P2),"[h]:mm")+TEXT((S2-R2)-(U2-T2),"[h]:mm")+TEXT((W2-V2)-(Y2-X2),"[h]:mm")+TEXT((AA2-Z2)-(AC2-AB2),"[h]:mm")))

Where C2 is your finish time on monday and b2 is your start time and E2 is your lunch finish and D2 is your lunch start....repeat for other days.

Hope that helps. I'm trying to now get my lunch break to not be fixed and either be an hour/or half hour at anytime.

mdobber77 said:
I am a manager. I like to keep my own time records for reference. Just in case. I have a straight time worked. example 8:00 to 16:30. I have to take an half hour for lunch off my time each day. How do i set this up in excel. I can get the hours worked + my time at lunch to total but how do i get the 30 minutes out? Please help

=EndTime-(StartTime+"0:30:00")

Re: Hours worked.....

I am trying to use the formula =SUM(TEXT((C2-B2)-(E2-D2),"[h]:mm") to track my time throughout the day and it works well if a range includes a lunch hour, but for other ranges (8:00 AM to 11:00 AM) it returns a value of 0:00

C2/B2=Finish/Start (WORK) E2-D2=Finish/Start (LUNCH)

What do I need to add to this to make non-break ranges work

Appreciate any help

Ben

Hello Ben, usually best to start a new thread....

That wouldn't be my first choice formula but I'm not sure why it wouldn't work when there's no lunch. What's in D2 and E2 when that happens, do those cells contain formulas?

It still works, it just removes an hour from each range regardless. I would like to add something like =if(range includes 12pm to 1pm, =SUM(TEXT((C2-B2)-(E2-D2),"[h]:mm"), =SUM(TEXT((C2-B2),"[h]:mm"))) not sure how to have the formula recognize if the range is 8am to 11am or if it's 11am to 3pm and then remove an hour for lunch.

I am a manager. I like to keep my own time records for reference. Just in case. I have a straight time worked. example 8:00 to 16:30. I have to take an half hour for lunch off my time each day. How do i set this up in excel. I can get the hours worked + my time at lunch to total but how do i get the 30 minutes out? Please help

This one's easy, since someone helped me with a similar problem recently.

=SUM(C2:C10)-SUM(B2:B10)-COUNT(B2:B10)*"0:30"

Replies
1
Views
122
Replies
3
Views
259
Replies
1
Views
69
Replies
5
Views
221
Replies
1
Views
435

1,203,506
Messages
6,055,806
Members
444,825
Latest member
aggerdanny

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back