Hello!
In an attempt to eliminate accidental overtime in my company, I am creating an Excel file that will allow hourly employees to calculate the time they need to clock out to avoid OT. Here is a bit of background, and then I will pose my questions.
We use the time system "Kronos," which will not allow non-system administrators to access the editing section, for obvious reasons. However, the system administrators do have the ability to punch in a "suggested" time to determine what the total time worked for the day (and, thus, week) will be. This is what I want to mimic. SIDE NOTE: We use a rounding system for our time punches, which makes things even more difficult.
I have created a formula that rounds each of the clock-in and clock-out times to the proper numbers, but I need to figure out if there is a way for the formula to work in reverse.
For example:
Clock-In: A1 7:27 am ---> B1 7:30 am
Clock-Out: A2 11:05 am ---> B2 11:05 am (lunch doesn't round)
Clock-In: A3 1:11 pm ---> B3 1:11 pm (lunch doesn't round)
Clock-Out: A4 4:54 pm ---> B4 5:00 pm
C1 = B4-B1 giving me a total time at work for the day
D1 = B3-B2 giving me a total time taken for lunch
E1 = C1-D1 giving me the time clocked in & paid for the day
So... here is my question:
I want to be able to have someone type in 8:00 (or 8.00, ideally) in cell E1 (or another cell, if that process is not possible) and have the formula spit out (perhaps in cell B4, if possible) what time they would need to clock out, as this is where most of the difficulty lies. Is this possible?
Please help, if you can! Thank you in advance!
In an attempt to eliminate accidental overtime in my company, I am creating an Excel file that will allow hourly employees to calculate the time they need to clock out to avoid OT. Here is a bit of background, and then I will pose my questions.
We use the time system "Kronos," which will not allow non-system administrators to access the editing section, for obvious reasons. However, the system administrators do have the ability to punch in a "suggested" time to determine what the total time worked for the day (and, thus, week) will be. This is what I want to mimic. SIDE NOTE: We use a rounding system for our time punches, which makes things even more difficult.
I have created a formula that rounds each of the clock-in and clock-out times to the proper numbers, but I need to figure out if there is a way for the formula to work in reverse.
For example:
Clock-In: A1 7:27 am ---> B1 7:30 am
Clock-Out: A2 11:05 am ---> B2 11:05 am (lunch doesn't round)
Clock-In: A3 1:11 pm ---> B3 1:11 pm (lunch doesn't round)
Clock-Out: A4 4:54 pm ---> B4 5:00 pm
C1 = B4-B1 giving me a total time at work for the day
D1 = B3-B2 giving me a total time taken for lunch
E1 = C1-D1 giving me the time clocked in & paid for the day
So... here is my question:
I want to be able to have someone type in 8:00 (or 8.00, ideally) in cell E1 (or another cell, if that process is not possible) and have the formula spit out (perhaps in cell B4, if possible) what time they would need to clock out, as this is where most of the difficulty lies. Is this possible?
Please help, if you can! Thank you in advance!
Last edited:
