Formula for Projected Clock Out Time

[Luckshadow]

We have moved to the Kronos time clock system. Our company has chosen not to round up or down based on when an employee clocks in or out but use the actual time entered. In order to minimize the amount of OT as well as make sure Kronos is tracking my time (I've already found issues), I am trying to create a spreadsheet that will tell me when I should clock out, in order to give me 8 hours in a day, based off when I clock back in from lunch.

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[TD="align: center"]H[/TD]
[TD="align: center"]I[/TD]
[/TR]
[TR]
[TD="align: center"]Day[/TD]
[TD="align: center"]In[/TD]
[TD="align: center"]Meal Out[/TD]
[TD="align: center"]Meal In[/TD]
[TD="align: center"]Out[/TD]
[TD="align: center"]Total Hours[/TD]
[/TR]
[TR]
[TD="align: center"]Monday[/TD]
[TD="align: center"]6:00[/TD]
[TD="align: center"]11:57[/TD]
[TD="align: center"]12:58[/TD]
[TD="align: center"]15:01[/TD]
[TD="align: center"]8.00[/TD]
[/TR]
[TR]
[TD="align: center"]Tuesday[/TD]
[TD="align: center"]5:08[/TD]
[TD="align: center"]10:58[/TD]
[TD="align: center"]11:57[/TD]
[TD="align: center"][/TD]
[TD="align: center"]8.00[/TD]
[/TR]
[TR]
[TD="align: center"]Wednesday[/TD]
[TD="align: center"]5:57[/TD]
[TD="align: center"]11:51[/TD]
[TD="align: center"]12:30[/TD]
[TD="align: center"][/TD]
[TD="align: center"]8.00[/TD]
[/TR]
</tbody>[/TABLE]

I have a formula that will auto fill out column I based on what is entered in E-H but I would prefer a formula for auto filling column H based on what is entered in columns E-F and column I being the default of 8.00 hours.

I found this http://transvec.com/kronos/ but it rounds up or down and I'm not able to see how the time is calculated.

A formula that can do that is beyond my capabilities. Does anyone know how to do this? Is this even possible?

 

[Computerman]

I think I understand what you are trying to do. The sequence of equations is first subtract the value in cell E from the value in cell F, that will give you the amount of time you worked before your meal break. Then subtract the value you obtained from the F-E calculation from 8.0, that will give you how much time you have left before hitting 8 hours. Then add the time you got from 8.0-(F-E) to the value in cell G and that will give you the time you need to clock out to hit 8 hours. So the calculation should be, =(G+(8-(F-E))). You will need to fill in the row numbers as I only included the Column references, which can easily done with either a loop or one time run from a command button.

Hope that helps,
Computerman

 

[jproffer]

In place of your 15:01 in column H, put this:

=(I2/24)+(G2-F2)+E2

(assuming your headings are in row 1)

 

[Luckshadow]

Yeah I forgot to put the row numbers in there. My first post. Sorry about that. Thank you for your assistance!

I think I understand what you are trying to do. The sequence of equations is first subtract the value in cell E from the value in cell F, that will give you the amount of time you worked before your meal break. Then subtract the value you obtained from the F-E calculation from 8.0, that will give you how much time you have left before hitting 8 hours. Then add the time you got from 8.0-(F-E) to the value in cell G and that will give you the time you need to clock out to hit 8 hours. So the calculation should be, =(G+(8-(F-E))). You will need to fill in the row numbers as I only included the Column references, which can easily done with either a loop or one time run from a command button.

Hope that helps,
Computerman

 

[Luckshadow]

In place of your 15:01 in column H, put this:

=(I2/24)+(G2-F2)+E2

(assuming your headings are in row 1)

That worked! Thank you very much!