Time Keeper in Excel

New Member
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?

Last edited:

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
I'm thinking a vlookup may work here. If you have a sheet with possible start times and end times in 2 columns and use this formula.

Code:
``=vlookup(B1,"your range with start/finish times",2,1)``

I think a 2 hr lunch break is a bit excessive...

Replies
4
Views
399
Replies
1
Views
399
Replies
2
Views
136
Replies
4
Views
801
Replies
2
Views
215

1,196,380
Messages
6,014,956
Members
441,857
Latest member
saraguchi2316

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back