Calculating Time at work minus time at lunch

[mdobber77]

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

 

[Brian from Maui]

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.

 

[Chitosunday]

Mdobber77,

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

 

[KyleG]

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.

 

[Aladin Akyurek]

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")

 

[bensko]

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

 

[barry houdini]

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?

 

[bensko]

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.

 

[lefty78312]

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"

Assuming that your ending time(s) are located in c2:c10 and your beginning time(s) are located in b2:b10. Format your answer as [h]:mm