Tracker
HOME    TRACKER    Excel

# Employees Time Sheet

## I am creating a employee time sheet. I am doing calculation. I used total hours formula is =(I5-B5+(I5<B5))*24 and also worked hours formula is =((I5-B5-(I5<B5))-(D5-C5+(D5<C5))-(F5-E5+(F5<E5))-(H5-G5+(H5<G5)))*24 Problem is don't show the correct time of total hrs & worked hrs Here i am attaching my timesheet format.

Related Forum Messages:
Manual Time Clock For My Employees To Punch In And Out
I currently use a manual time clock for my employees to punch in and out. I then use Excel to tally their times. This is very tedious and error prone.

Is it possible to use Excel as the timeclock itself? I know that the employee can enter the time in a spreadsheet manually. But this also opens the door for error and potential dishonesty.

What I am looking for is, if the employee can punch in and out by doing a keystroke and Excel uses the computer's clock to timestamp.

Count Of Employees That Worked Within Time-frame
I am trying to do a timesheet spreadsheets that lists employees clockin and clockout times

Name Start End num hours worked
Cory 02:00 04:00 2
Jack 23:00 05:00 6
Fred 10:00 17:00 7

and then go through the list and and count the number of employees in a certain range.

time range number of employees working
05:00-06:00 1
06:00-07:00 2
07:00-08:00 6
08:00-09:00 5
09:00-10:00
10:00-11:00
11:00-12:00
12:00-13:00..............

Countif Formula: Count Of Employees Next To The Each Manager's Name Who Login Time Is Less Then 8 Hours
I have a sheet in which in Coloum A I have manager names, then in Colum B I have list of Employees reporting to that manager, then in Column C, I have total login time of each employee which should be => 8 hours. In a new sheet, I want to get the count of employees next to the each manager's name who login time is less then 8 hours.

INDEX And MATCH To Pull Back Employees Hours From A Master Sheet
I am usuing INDEX and MATCH to pull back employees hours from a master sheet, I am asking excel to Index, matiching firstly the agents name and then the day of the week (Formatted: Mon,Tue,Wed,Thurs,Fri,Sat,Sun). This works perfectly for every day of the week using the following formuls except for Sun where it returns #REF!

=(INDEX(Hours!\$B\$4:\$G\$166,MATCH(\$F\$1,Hours!\$A\$4:\$A\$166,0),MATCH(C7,Hours!\$B\$3:\$H\$3,0)))

Time Sheet :: 12 Hour Time Format With AM/PM
I found a formula for calculating time in the HH:MM AM/PM

=IF(B1<A1,B1+1,B1)-A1

This formula was to give me total hours in the cell for which it is entered, and cell format for the formula was in military format.

I cannot find this post. The formula worked in OpenOffice Calc program, but when it came to converting to excel, I came up with #value!

I want to enter the time in 12 hour format and using am/pm to designate. I am making it for someone to make work schedules with and they do not know military time.

Time Sheet Lunch Time Calculations
I am making a time sheet for work. It should be very simple but cant get it to work. I want time in, time out, lunch column, and Total. I don't want a lunch in and a lunch out though just a section where I can put my time in :30 minuets :45 or what ever. I also want it to work. on a 12 hour clock AM/PM .

Here are my column's
B2 Is Time In
C2 is Time out
D2 is Lunch time ( decimal ? )
E2 is the TOTAL with lunch deducted.

Protect Workbook From Ex-employees
How can I protect by workbook from ex-employees and the competition from using (I am aware of the weak security measures Excel offers, but something is better than nothing)? Each worksheet is password protected to slow down novice Excel users. However, ex-employees know the password. I was thinking if there was a way to have a changing password (which only a couple people would know) or have the worksheet look for a hidden file on our network, that one of these methods (or another) might work.

Divide A Budget Of 100k For Each Of My Employees
how I would be able to divide a budget of 100k for each of my employees.

If I have employees making anywhere from 20,446.40 - 69,659.20 How can I give each employee a portion on these funds?

For example if:
Lisa makes 20,446.40
Tom - 24,448.20
Liz - 34,854.60
Mike - 44,564.80
Jan - 62,061.48
Dan - 54,324.20

What would be the precentage that I could give each employee?

Ranking Employees By Start Date
I am trying to rank employees by seniority date. Some employees share the same seniority date. They should have the same rank. I took a formula from another thread =RANK(C2,C\$2:C\$8) and applied it to my worksheet and it worked out great except that the ranking skips the next number (in this case 4)...........

Show Latest Information For Employees
I have an excel file with 2 tabs. tab 1: Active and Tab2: tenure. Tabe 1 has the following column headers
Manager
Employee Name
Dept
Assignment
Original Start Date
End Date
Bill Rate

The Employee names may be repeating in this tab becasue whenever the employee is extended for a new assignment he has a new start date and new end date and new assignemtn and rates....This helps track the historical information about the employees. TAB2: (tenure) also has the following column headers...................

Delete Expired Dates From A List Of Employees
Basically I have a list of employees. This list is simple in that it shows their off time "From:", "Till:". in columns to the left of there name. So in Cell A:A would list all of there names.. In the example sheet I have just a few listed. To the left of their name if "From:" (column B) and then "Till:" (Column C). And this From, Till repeats itself for several columns so that multiple off-times may be made for each employee.

Basically what I want to do is delete any sets of dates that are expired (based on the "Till:" date) and shift the remaining dates over. So in other words, if Sam has a date in there of 8/1/09 to 8/1/09 meaning he is going to be off for just that day, and today's date is 8/2/09, then that "from" and "till" date of 8/1/09 to 8/1/09 would get deleted from his list and the remaining dates (if any) would get shifted to the left.

Hopefully I have provided a good enough explanation of what I am trying to do here.. Please let me know if you have any questions! I have provided an example the reflects my setup (and the setup can be changed if it makes anything easier)

Using COUNTIF To Determine # Of Employees Working Each Hour
I'm using Excel to schedule employees shifts at my restaurant. Column A is their name, Column B is their start time, Column C is their end time. I'd like to be able to count the number of employees working during any given hour of the day to assure I've got enough staffing each hour. I'm thinking it will require a COUNTIF function but I can't figure out how to make it work. I've attached a sample sheet.

Ranking Error :: Formula To Rank Employees
I had tried ranking formula to rank employees on some cirteria it was working, but if there is any tie between two employees then the next rank is not displaying for example in Ach Rank : rank 5 is missing because there is tie is between two employees is rank 4. Request your suggestion and help on this for overcoming the issue.

Formula used for ranking : =IF(B2>=75%,RANK(B2,\$B\$2:\$B\$41,1)-COUNTIF(\$B\$2:\$B\$41,"

Nested IF Formula: Calculate The Commission For My Employees
I am trying to write a command to calculate the commission for my employees. There commission is based on the spread between sale price and cost. For example:

If Profit is between \$1.00 and \$2.00 - commission = 15%
If Profit is between \$2.01 and \$4.00 - commission = 20%
If Profit is between \$4.01 and \$6.00 - commission = 25%
If Profit is > than \$6.00 then - commission = 30%

I am able to calculate the first level ex: =IF((C3-B3)<=2,"15%") It Displays the 15% in the formatted cell. (C3-B3 is the profit spread). How can I include the other 3 commission levels in the formula to display the correct commission % based on profit spread?

Filter Data To Show Only Employees Who Are TRUELY Tardy
I have an excel sheet with over 4,000 rows of records regarding our 80+ employees clocking in. There are three columns:

A: Date (MM/DD/YYYY)
B: Time (HH:MM:SS)
C: Employee Name

Our employees are expected to be at work (and clock-in) at or before 8am.

At first, I just filtered the data to display only rows that have a time value of between 8am and 9am (the hour when most people are tardy). However, I now realize this is not a very accurate way of reporting tardiness.

Our forklift driver, for example, almost always arrives at 7:45am, but then works around the entrance and has to scan his card (clock-in) several times throughout the morning to get back into the building. So if he's moving something outside and comes BACK inside at 8:30am, my current "filter" inaccurately reports him as tardy for that day.

Is there some way to filter this data to first reduce it down to their earliest clock-in for the day, THEN isolate only the 8am - 9am data (if we did that, the forklift driver problem would be solved because his only time for that day would be 7:45am - his TRUE clock-in). The forklift driver is just one example. Many other employees occasionally go in/out of that door and end up clocking in multiple times, so are getting wrongly reported as tardy.

Display Employees List Whose Passport Going To Expire In Given Period
I have my Employees List in one Sheet and in another Sheet2 I want to Display those Employees Names and List whose Passport going to Expire in the dates entered in Sheet2 A1 and B2 Cell.

Record Employees Sick Days & Holidays
I have a calender with days in the columns and months in the rows. However I have 3 rows for each month. I'm using text and values combinations in the cells to record data

Example First Sheet

Feb Rows, 11,12&13
Columns C,G Dates 1st to 5th

Therefore 1st of Feb has 3 cells to enter data, ie Basic Hours, Sickness, Holiday

Currently I'm defining a name range for each month, ie DFeb. Currently I'm recording each of the rows into columns in another sheet and the date in the row, by using a Hlookup on the date and a vlookup on the month

ie Second Sheet...............

Report Of Employees By Date, Holidays & Sick Days

In the data tab I have my base data, which is dates across the top and names down the side. I have information in the middle such as 'H8' which means that the person was on holiday for 8 hours on that particular day, and 'S8' which means they were sick for 8 hours on that day.

I want a macro that will rearrange the information in the table and put it into the report format which is in the report sheet.

If you see the spreadsheet you will probably get a better idea of what I am trying to achieve.

Sumproduct And Sum Array Formula (calculate The Salary Payments To Temporary Employees In A Particular Work Unit)
I have a spreadsheet representing a month where I am trying to figure out different scenarios for employees. One scenario is that an employee could have to move to a temporary position. In that case, I need to calculate the salary payments to temporary employees in a particular work unit. I've tried several different approaches to this problem, but am still getting the error.

One method has been using this sumproduct formula:

Making A Time Sheet
I made a time sheet. In this time sheet i made a macro where when I press ctrl+t it fills in the date in a column and the time in the column next to it. So to start my day I would press ctrl+t and to end it i would goa column over and repeat the macro. and then i made it so that in the fifth column it adds the 2 times together with this formula "=IF(E27>0,(IF(G27>E27, G27-E27, 1+G27-E27)),"")" formula works fine unless you make a "mistake" and change the ending time manually to a time that is early then the starting time but on the same day. So I wanted to see if there is anyway of the total time column can return an error message if the ending time is earlier then the starting time by looking at the date in both date columns. Im going to attach the workbook to this post. on the tabs on the bottom it is under time sheet. and i think the macros should transfer over too. ctrl+t is the time macro if you want to try it out.

I may have not been so clear on explaining it so ask any questions if that is true.

Time Sheet Total
I am putting together a time sheet for my peoples and can not figure out how to total the hours and remain in hour format, so I have the first employee's start time in B10, Stop time in B11 and total in B12, then the second in C10, C11 and C12 etc etc a grand total of all employees in H12 how would that be entered?

Time Sheet Schedule
I need to do is calculate the hours between two clock times minus 30 minutes.

Example:
Cell A1 - 4:00am
Cell B1 - 16:30
I need Cell C1 to say 12 (hours)

Also:

Cell A2 - 17:30
Cell B2 - 02:00am
I need cell c2 to say 8 (hours)

Time Sheet Formula
What iam chasing is a formula or something that when you enter hours for week (which is in AD column) *standard rate is 38 *

if you say put say 48 in that column it breaks the time down and puts 38 there and in column AI puts 3 hours and in column AJ Column puts 6 hours.but calculates each one with the dollar value.

Time Sheet Error
i have a time sheet that i just , was testing times and enter the time 02:00 pm to 10:00 pm when this is done 5 times for five days the HRS are ok = 40 but in my cell for over time witch should be 0:00 has ############################################ in it.
heres my codes, it only dos it on that time fram, if it go in to overtime its ok then. if i make it 6 days and i try 4 day at 2 to 10 and 1 day at any other time and its ok all works, why do 5 days of 2 to 10 do thsi and can it be fixed?
["Formula"]
'column "W"
=IF(T34-S34+V34-U34

Employee Time Sheet
I have problem to make an excel evidence which will track total working hours of employee during the month, how many times employee was late for work and how many times employee was on lunch break longer than 30 minutes.

And I made table which count all these things but problem is Saturday because on that day start work is 9::00 AM and all other days is 8:00
Formula for counting how many times employee was late is:

COUNTIF(D8:AC8,">0.336805555555555555555555555555")
where D8:AC8 is range where are all start work times in month and 0.336805555555555555555555555555 is 8:00 AM (serial number which is equivalen of 8:00 AM)
How to improve this formula to count properly because now it counts as a late when employee start work on Saturday on 9:00 AM (and that shouldnt count as a late)
[IMG]file:///C:/DOCUME%7E1/Nesa/LOCALS%7E1/Temp/moz-screenshot.jpg[/IMG]

Time Sheet Calculations
Attached is a layout that I am trying to get working.

the 1st tab is the input sheet, I want to be able to post input on that sheet and have it transfer to the other tabs in the appropriate fields.

I'm having a problem mostly with the vlookup to get the data into the right cells on the other tabs.

Protect Each Sheet At One Time
Is there anyway to protect each sheet at one time? I have about 70 sheets and I dont wan to go to each and protect them. They all have the same password so I hope thats not a problem.

Weekly Time Sheet Calculations
I need to calculate the total hours worked for a series of 22 locations. Each Location has weekly allocated hours ranging form 0 to 80.

For example:
User1 worked 4 Hours on Monday at Location1 and 4 Hours at Location2.
User2 worked 4 Hours on Monday at Location1 and 4 Hours at Location3.

I am assuming I will need to use VBA for this calculation.

Return The Value Of The Shift Time On Other Sheet
I have a worksheet that contains a 5 week shift pattern for workers. It is briefly laid out as:

JanFebAB
111330-21300700-1400
221230-20300700-1400

Months Jan to Dec 09 are there and shifts are divided up to ABCDE. I need another sheet to return the value of the shift time i.e. 1330-2130, probably via a lookup?? In my second sheet I have the Day number, the month and the shift letter.

Find That Time In Another Sheet (which Is In The Same Workbook)
I want is when a user enters a time into a specific cell I want Excel to find that time in another sheet (which is in the same workbook) and give out the details of the cell which is next to it, see attached image file.

Name Sheet With Date & Time
I want to rename a tab in Excell to the current date and time. Below is my code. Everything seems to work OK except that instead of insearting a time like this, "11_06 at 4_51 PM" it inserts "11_06 at 4_51 P11" I have no idea why its insert P11 in there.

ActiveSheet. Name = (Format(Now, "mm" & "_" & "yy" & " at " & Format(Now, "h_mm AM/PM")))

Go To A Pre-specified Cell Each Time You Click On The Sheet
a macro that automatically puts the cursor on a pre-specified cell everytime you click on that sheet?

Macro Stops Every Time It Deletes A Sheet
I have a macro that that stops every time I’m trying to delete the sheet. I get this message. “Data may exist in the sheet(s) selected for deletion. To permanently delete the data, prese Delete.” and I have to manually click “delete” to continue running the macro. How can I avoid this stop so that macro runs thru it without stops?

Copy Data From Userform To Sheet, On A New Row Each Time
I have designed a userform that allows teachers to input assessment grades and calculate overall module grades based on these...

I'd like to develop a macro that would then allow the teacher to click a "save" button on the userform, triggering the transfer of the information on the userform into the next sheet and then clear the contents of the userform ready for the next calculation.

I have made an attempt; unfortunately I have very limited experience of Excel and am therefore running into difficulties; the macro is as follows: .....

Do Operation On All Sheets Except The First Sheet In The Book Every Time I Update It
I have a workbook that I would like the same operation on all sheets except the first sheet in the book every time I update it (which is once a week).

I get data (currency format) for all of my employees on a weekly basis. Unfortunately this data is shown as a negative number and I need to invert these values. (So I use the formula below)... but I have to do this manually for about 35 sheets and I figure I could automate this somehow with a macro. Here's what I do usually and what I would like the macro to do automatically:

I want to insert a column after column G.
I would like to enter the following formula into H2:

=IF(G4="u", "u", IF(G4

Time Sheet :: Start / End And Difference Between Two Times
I am trying to create a time sheet where there will be a start time - end time - number of hours (difference between the 2) - pay rate - total pay, but am getting strange results!

Can We Know Which Button Was Pressed On The Sheet When The Buttons Are Created During Run Time
I have created a button name "Add Power Source" on excel sheet that adds a table and a button(named "Add Row") to the sheet.Whenever the user clicks the "add power source" button a table and a "Add new row " button is created .This "Add new row" button is used to add rows to the table.
Now suppose the user has pressed the "add power source" button 5 times,so that there will be five table on sheet and each having a "add new row " button associated with it.When the user wants to add a new row to the table 2 say .How will I come to know in which table the rows are to be added using VBA.

Link Cell From 1 Sheet To Another: Pull Data Based On Time
I'm trying to make a floor plan which shows what seats are available depending on the time it is. So I made sheet1 = to the map (where the seats are located), sheet2 - rows = seat #'s, columns = Time (broken down by half hours). Now i have conditional format to show "t" in red (which means taken), "f" in blue (which means free), and "n" to show in gray (which means not working).

What i need is example - Seat # 200 in sheet 1 to show red, blue, or gray depending on the value in the row that corespond to this seat and the column that corespond to the current time.

Time Format- Spread Sheet To Calculate Call Duration
I am setting up a spread sheet to calculate call duration it would be a great help if data could be entered using one hand IE use decimal point on the number pad instead of the colon is this possible. I will need to subtact start time from end time to get the duration

Email Single Sheet. Run-time Error 438
I'am trying to send a single worksheet by copying it out of workbook and saving it, and after sending I want to delete the temporary file. But I get a Error message: 'Run-time error 438: 'Object doesn't support this property or method

Sub SendWeekrapport()
Dim wb As Workbook
Dim bestand As String
Call SelectWeekrap
Application. ScreenUpdating = False
ActiveSheet.Copy
Set wb = ActiveWorkbook
With wb
.SaveAs Range("AG3").Value & "bestand" & Range("b2").Value & ".xls" 'amend to suit
.SendMail addr, " Attached Timesheet for " & Range("B2").Value

Date Format: Extract Only Time And Paste It To Cell A56 In Same Sheet
I get one report.xls workbook by FTP. This book contain date and time in cell A14 in sheet1. e.g. 30-04-2006 19:38:00

I need to have macro to extract only time and paste it to cell A56 in same sheet. How can I do that. The date format is 30-04-2006 19:38:00 and it may be 05/05/2006 7:15 am. Or it could be any type of date format. Please send me how can I work with different date and time format to extract the only time and paste to other cell.

Lock Cells, Protect Sheet With Password & Time Stamp
I have a worksheet with employees names and their overtime hours worked that week. I would like to add some code to a button that will prompt for a password. If that password matches a predefiened password then lock a range of cells and place a time stamp next to the button and maybe change the button color of the button to Red. Now if the person with the password wants to change the locked cells, they can click the button again and enter the password and the button will turn green so they can make the changes. I would like the time stamp to be updated every time the cells are locked. Also wanted to have one button for every work week so about four buttons per worksheet. Here is an example of the worksheet. The range of cells I want to lock are the ones in bold.

Display End Time Automatically Upon Entering Start Time And Time Usage
I have a worksheet which contains START TIME in column A, then TIME USAGE in column B and END TIME in column C. User enters start time, followed by the number of time usage in minutes, how could i possibly display the end time automatically in this scenario? how do you add the entered time usage to the start time to display the end time? Say if I enter 1:00 AM at start time and 00:15 minutes on time usage, how can 1:15 AM be displayed on the end time automatically?

Worksheet Open Event: Macro Run Every Time A Sheet In A Workbook Is Accessed
Is there a way to have a macro run every time a sheet in a workbook is accessed? Something similar to the workbook open event, but for worksheets.

Run Time Error 1004 When Selecting Range On Another Sheet Using A Command Button
Run Time Error 1004 when selecting range on another sheet using a command button. I have created a command button that plays the following macro.

Worksheet Protection From Editing And At The Same Time Allow All Data Source Links Within The Sheet To Be Refreshed
how to protect a worksheet from editing and at the same time allow all data source links within the sheet to be refreshed? I need my users to be able to refresh all data links as required but not be able to change the worksheet in any other way.

Add Drop List Box Or Combo Box In Yearly Time Sheet
how to add drop list box or combo box in this yearly time sheet so every employee has his own record in this time sheet so when ever i select name from drop list all info changed, i did include table in sheet 1 as an example.

Trying To Copy A Hidden Sheet Using Macro, Run-time Error "1004"
Basically my code is bombing at the red highlighted code. Error: "Run-time error '1004': Copy method of Worksheet class failed"

Sheet "Blank Form" is a hidden sheet. Can I alter the red section of code to work while being a hidden sheet? I tried a few variations but nothing seems to work..

Subtract Start Time From Finish Time Return Hours Worked As Number
I have a user form with textBox1 = start time (entered as "[h]:mm") and text Box2 = finish time (entered as "[h]:mm"). I would like textBox3 to display the difference between the start time and finish time as a general number!

For example
Start time: 21:00
Finish time: 06:30
Hours worked: 9.50

Start time: 12:30
Finish time: 23:00
Hours worked: 10.50