Time Clock Help

[livifivil]

I am supposed to create a grid that will track latenesses, call outs, holidays, etc.. Until this point, we have just printed out everyone's time sheets and just entered their clock in and clock out times. Then, manually highlighted the cell red if the employee was over 15 minutes late, called out, left early, etc.. Obviously, i am sure there is a more efficient way of doing this. I figured i could build a work book that would have the official schedule on the first tab. On the second tab i would have the in/out times for the week for each employee entered manually from their time sheets. When a time is entered on the 2nd tab, it will reference the first tab and highlight itself red if there is a discrepancy, i.e. a call out, a holiday, over 15 min late, etc... The 3rd tab could be a year to date running total summary page for each employee. I am just not quite sure how to execute this. I am sure there is already some sort of template out there for something like this although i don't mind building one from scratch. Can anyone lend me a hand with this, thanks.

[Special-K]

Take a look at Conditional Formatting

[livifivil]

How would conditional formatting keep a year to date record of lateness and absence? How would it reference the office schedule on the first tab?

[Special-K]

Conditional Formatting just covers the highlighting of red cells you asked for.


Use something like

Sheet1!A1
Sheet2!B12
Sheet3!C14
etc

to refer other tabs

[livifivil]

Alright, how about we just forget about the conditional formatting for now. I am pretty handy with that so I really don't need much help with that aspect of the grid. What I need the most help with is the time comparisons and the summary page. Would it help if I posted a sample grid? I am going to put one together real quick and see if that will help.Standby.

[royUK]

To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook. Don't upload a picture when you have a workbook question. None of us is inclined to recreate your data. Upload the workbook and manually add an 'after' situation so that we can see what you expect. In addition clearly explain how you get the results..
To attach a file to your post, you need to be using the main 'New Post' or 'New Thread' page and not 'Quick Reply'.
To use the main 'New Post' page, click the 'Post Reply' button in the relevant thread.

On this page, below the message box, you will find a button labelled 'Manage Attachments'.
Clicking this button will open a new window for uploading attachments.

You can upload an attachment either from your computer or from another URL by using the appropriate box on this page.
Alternatively you can click the Attachment Icon to open this page.

To upload a file from your computer, click the 'Browse' button and locate the file.

To upload a file from another URL, enter the full URL for the file in the second box on this page.
Once you have completed one of the boxes, click 'Upload'.

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

[livifivil]

Here is the file. Although, I am starting to realize that this may be a little too difficult to maintain. The first tab has the schedule for week 1 and week 2. We work on a biweekly schedule here. The 2nd tab has the actual time cards. On that tab I will keep all the actual clock in/out times. I will just keep copying and pasting one schedule below the next for the rest of the year. The 3rd tab will just keep a running total of how many times each employee was late, called out, had a holiday, was sick, etc...

Let me know if this system would work. If not, is there an easier way to get to the same result? Any help would be appreciated, thanks!!!!

[livifivil]

I posted a sample workbook. Can anyone help me out with this? If not, can someone point me in the right direction? I assumed that a work schedule that calculates YTD lateness and absence would be something pretty easy to research but i haven't had much luck.

[darknation144]

Your asking for a lot of work. It will require a lot of time. It would be better to ask for help on one aspect. I'm trying to have a go at your time difference problem first.

[darknation144]

TimeDiff.xlsx

So I've got the red highlighting working. I need some help on how to CountIf late. I know you can do it with VBA counting the back ground colour but I'm sure you can compare the ranges and count if they are late just in a formula.

I've sorted the count for Call outs, Holidays and snow days.

Also need help for left early.

I had to delete one of the tabs as conditional formatting only works within one worksheet.

[livifivil]

I did not get a chance to look at the grid yet. I fell behind at work and i havent been able to check the site as frequently as i like. As soon as i take a look, i will reply. Thanks a lot for the help so far though.