Formula to fill C5 with a time that makes J5 equal 8 hours based on time put into B5

[possumbarnes]

I have a time sheet that I send out to a team to use to track their time. It is a team of field techs that are always on the road going from site to site. Currently, you clock in when starting work, clock out for lunch, clock back in after lunch, and clock out at the end of the day. There is also a couple extra clock in and out cells for when we get called out after hours. Each row is dated and totaled in column N, and every 7 rows are totaled in the 8th row for weekly totals.
Management is super picky about clock out times making for 8 hour days so I want to make this sheet to basically tell the tech when they need to clock out at the end of each day based on their current inputs (because some of these guys are NOT the sharpest knife in the drawer, if you know what I mean).

Basic layout: Columns B, D, F, and H are all for their clock in times. Columns C, E, G, and I are for clock outs. Column J uses an IF formula to total the times in columns B thru I. Columns K, L, and M are used to show paid time off, overtime, etc. Column N is the total hours for the day with a simple sum of cells J thru M.

Now that I've thoroughly confused you, here's what I need: I need a formula to take a manually input time from cell B5 (Clock-in time), figure out what time needs to be put into cell C5 (Clock-out time) that will make cell J5 (simply subtracts B5 from C5) equal 8 hours, then have that formula automatically fill in cell C5 with that time (thus, showing the user exactly what time they need to clock out for it to be an 8 hour day).

[Richard Buttrey]

Hi,

Presumably

Please Login or Register  to view this content.

[possumbarnes]

Sorry, I wasn't clear enough. I attached a .png file showing what the sheet looks like. In the example on row 5, the tech clocked in at 8:00. He worked for 4 hours and clocked out at noon for a half hour lunch then clocked back in at 12:30. I need a formula hidden in one of the outer columns on that row that will take the input times, calculate what time is required to make it an 8 hour day, and automatically fill in the next cell with that time (in this case, it would be 16:30).
Can this be done without using macros?

[Richard Buttrey]

Hi,

Please upload the workbook. Pictures are rarely much use.

[possumbarnes]

Ok. Thanks. Here it is. I am an amateur at excel formulas, but what I have here works. I'm just trying to make it better.