Time To Leave

[Guest]

Hello. I've got a spreadsheet that calculates the time I've worked for the
two weeks of each pay period and approximate pay for those weeks. However, I
just came across the goal seeker function and it gave me this great idea, I
want to be able to put in the time I clocked in, the time I clocked out for
lunch, and the time I clocked back in from lunch, and have the computer tell
me what time my 8 hours are up. (I'm not allowed to go over 40 in a week, but
my schedule is flexible in that I can arrive early and take short lunches and
leave early, etc.) I know I can goal seek this but I want to have a formula
to do it for me so I don't have to. I'm currently entering the time in in
military time, so I don't know if that makes a difference or not.

 

[Guest]

Assume...
'In' is Cell B2
'Out' is Cell C2
'In After Lunch' is Cell D2

Time to leave would be calculated as...
=+D2+(8/24)-(C2-B2)
Format in Military time if desired.
(8/24) indicates 8 hours worked out of 24 is time to leave!
HTH,

 

[cvolkert]

I don't know if you need to get as fancy as a Goal Seek to figure out
when to leave. If you are just wanting to calculate at what time each
day you reach 8 hours, then use the following formula for your
clock-out time:

=Clock-in + 1/3 - Lunch-out + Lunch-in

Essentially, this takes the time that you start work plus 8 hours (1/3
of a day) and then tacks on the amount of time you are gone to lunch.
Let me know if you have questions - Chad

 

[Guest]

Both of these posts were exactly what I was looking for, thanks to both of
you... I added in an if function to automatically put in 5:00 if there hasn't
been a lunch clock-in yet.