Employee Time Sheet

Apr 8, 2009

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]

ADVERTISEMENT

Employee Time Schedule, Calculations

Sep 28, 2009

I have tortured the cells into producing the output I want...

Namely time(s) in and out, with the hourly totals - subtracting 1/2 hour
for shifts over 6 hours.

I however have a couple of questions about glitches the approach I am using give me.... I will outline as best I can: wish I could upload a sample...

1) the user inputs (in the time format) the starting and ending times for the individual ie. 9 A = 9:00 AM ... 1:30 P = 1:30 PM simple

2) after the user inputs the total formula for each day is calculated:

=IF(D3="","",(IF((D3-C3)>.25,((D3-C3)-0.020833),D3-C3)))

That leaves the cell null if there is no time in the day...
If there is time, checks to see if more than 6 hours...
If so, deducts 1/2 hour, else does the difference calculation
This does work, but is it the elegant or simple way? recommend?

3) At the bottom of each day totals are calculated by this :

=Text((SUM(E5:E36)),"[H]:mm")

This also works to sum the individual totals to the bottom
But is this the best way to work with the times?

4) the individual then inputs the Actual Hours worked by the team...
And the sheet does a variance calculation between actual hours
Worked vs. Scheduled hours...

=TEXT((D38-D39),"[H]:mm")

Now this is a problem...cannot do negative times....need help!

5) Am having a problem with totaling the individuals weekly total using ...

=TEXT((E3+I3),"[H]:mm")

View 9 Replies View Related

Import The Individuals Data From The Master Sheet To The Individual Sheet Based On The Employee Name

Feb 6, 2008

I have a master spreadsheet that list several columns about employees(name, date, event name, etc.). Then I have an indivdual sheet for each employee. I am trying to import the column information for each employee onto their individual sheet based on their name. It is possible to import the individuals data from the master sheet to the individual sheet based on the employee name?

View 9 Replies View Related

Employee A Date Line Of Time Of When Pieces Should Be Finish

May 29, 2014

1-The employee takes 13 minutes to build each piece

2-The employee starts at 7:15 am

3-The employee goes to lunch from 12:15 pm to 1:00 pm

4-I need to give the employee a date line of time of when pieces should be finished. Taking into account the time he/she takes lunch.

View 2 Replies View Related

Employee Schedule Lookup Time Value And Return Name For Daily Tasks

Oct 10, 2013

I'm trying to auto input names from my schedule into dailytasks for my servers. Sometimes the amount of servers on a specific day changes(i.e Mondays 3 to 5 servers and Friday - Sunday there is 4-6 servers on) and times could change also as of now it works with

=VLOOKUP("6:00",AC$25:AJ$46,8,FALSE)

Not sure how to set up lookup value and return the name

View 4 Replies View Related

Search By Employee Id- Display Rows On Another Sheet

Aug 18, 2009

On sheet1 I have this:

employee ID Date hours item1 item2 item3 item4

So it's 8 columns and the row count can vary.

This list on sheet 1 has the persons employee id and various bits of info. There will be multiple rows with the same employee id. This data is not sorted in any particular order, but I'd rather leave this sheet untouched.

On another sheet, I would like the user to simply type in a number for the employee id (I don't want to use msg box, just a cell is fine, maybe a button form to click search to run the macro) and it lists all their hits, however many rows that may be. Again I can't sort the original list so the employee might have hits scattered throughout the main list.

So they would enter the employee id and below that they would get all the rows with all the above data where they get a hit. One concern I have is say the first search for employee 123456 turns up 18 results. Then they search for employee 123444 and it only returns 8 results. I don't want the last 10 rows of employee 123456 to be still present. Basically a search that searches another sheet and returns any hits, and it clears the list of previous search results so for each search you only see the results of that search and not old data.

View 9 Replies View Related

Automatic Timesheet Creation For Individual Employee From One Master Sheet

Apr 26, 2014

How to come up with solution for preparing monthly time sheet for each employee from master sheet automatically by entering details in master sheet (Master sheet is all employees attendance sheet).

Just by entering data in master sheet it need to copy data to individual employee sheet. If I enter new employee name in master sheet can it creates one new sheet with same format by itself?

Data are
Project, Emp Name, Position, Date, Day, Time In, Time out, Overtime or under time (if any)

I have total 30 employees and it is increasing day by day

View 1 Replies View Related

Employee Leave Tracker & Check Leave Time Is Valid

Feb 27, 2008

I need to create a leave tracker wherein i will get the names of the person who want to take leave in that particular month. I have prepared a calendar to make an entry. I need to take care of the following things

1) The person must have sufficient leave balance available to take the leave. so i have to first check if he/she has sufficient leave balance.

2) There are four team leaders and each team has around 13-14 members. so if lots of people from the same team apply for the leave then they will not be eligible for leave. this i want to know in percentage as to how much percentage of people are taking leave from that particular team.

View 5 Replies View Related

Excel 2011 :: Making Time Sheet - Converting Numbers To Time

Jul 11, 2014

A client buys 500 minutes of my time. In one week I spend 340 minutes on the account. I'd like a column to show Time purchased (say 500 Minutes) Time spent and a final section showing time left (but showing negative values in red)

I hope I've explained this ok but here is an illustration of what I'm trying to achieve in Excel 2011.

[URL] ...........

View 2 Replies View Related

Using Userform To Calculate Time To Sheet From Start Time?

Jun 19, 2014

I have a userform that time stamps on my userform as soon as i open the form, is there a way that when I submit, that the amount of time that I was on the call to be put in my column on my worksheet as minutes?

View 2 Replies View Related

Time Sheet :: 12 Hour Time Format With AM/PM

Mar 13, 2008

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.

View 11 Replies View Related

Time Sheet Lunch Time Calculations

Aug 12, 2009

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.

View 9 Replies View Related

Calculating Total Time In One Sheet And Pasting Results Automatically In Another Sheet

Apr 21, 2014

I have an excel workbook which requires us to enter the log details of some sites and then a report is generated using these logs (report attached) . [URL] .......

I want that excel should automatically identify the site and calculate its outage with the following formula:

Outage %age= Total Outage(min)/100.80
and add it in the respective column while identifying the outage.

e.g. if outage is of power then the calculated %age outage should be pasted in the power outage column in front of the respected site.

e.g. in the sheet attached above the first row in the first sheet states that SKU2326 was down for 50 mins

Now %age outage will be
Outage %age= 50/100.80
Outage %age=0.496

Now as the outage is related to power so this outage should go into power column in sheet number 3 labelled as "Network Total Up time Week#17"

Now the next time SKU2326 becomes down it should be added with this 50 mins and the outage calculated as presented above.

View 1 Replies View Related

Getting Name From Employee#

Sep 29, 2009

This is what I am looking to do and I am having an issue with it.

worksheet "beeble"

Column B has all the employee numbers listed from B3 to B100
Column C needs to have the emp name put in to them based on the emp number.

worksheet "weeble"

This sheet has the list of employees with their Emp#

A2 down to A99 is the number B2 on down is the name that belongs to the emp #

At issue is sheet "beeble" changes day to day depending who is scheduled to work in a certian area, otherwise this would be quite easy.. It is very easy for us to put the emp# in instead of the emp-name, so that is why wwe would need to be able to pull this information from the other sheet.

This may end up being very easy, but it is beyond me, and I cannot find what I need from a book, as I spent last night on here searching and reading through a few of my books..

View 13 Replies View Related

Employee Number

Sep 3, 2007

I have a list of employees that i need to simplify into groups as listed below

Employee No's
1-19
20-50
50-199
200-499
500+

In my list of data (attached) that i receive i currently manually count the employee numbers and then put them into the relevant bands dependant on the employee sizes. Example:

Employee List
4
28
35
46
503
376
2000

Employee No's
1-19 - 1
20-50 - 1
50-199 - 3
200-499 - 1
500+ - 2

View 11 Replies View Related

Name Appears Instead Of The Employee ID

Sep 11, 2007

SEE ATTACHED FILE

All,

I have a spreadsheet that lists each employees hours for the last 4 pay periods...each is in it's own row I am trying to find out their average.

I am using the subtotal function to average their hours and that works fine. BUT...my boss doesn't want to see 4 rows for each person. He just wants to see one row for each person and when I collapse the rows, it only shows the employee's ID, not the name (because that's what I told the subtotal function to do...add subtotal after every change in employee ID).

name appears instead of the employee ID? That's useless to him because he hasn't memorized all the Employee's IDs.

View 13 Replies View Related

Projects Per Employee

Apr 17, 2008

I was given a task of calculating bonus for number of projects per employee. The maximum number of projects per employee is 30 and they have completed different number of projects. Data is as follows:

Column A - Name
Column B - Date
Column C - Project

Employee name repeats one row per project and project repeats as they are working with it.

I need to list individual employee names in column D and the number of projects each employee has done in column E. A project can be saved many times thus creating many rows for that same employee. Do you think it is possible to accomplish this. One formula for column D and one formula for column E. If needed I can attach an example file or take a screen shot of it.

View 9 Replies View Related

Ranking The Employee

Feb 2, 2009

i have two col A and B

In col A name of the employee
In col B there production for the Month

I need in c col to rank them that according to there production for the month like 1,2,3, etc.

View 9 Replies View Related

Employee's Project Report

Jan 26, 2010

I have created a spreadsheet in Excel where it gives me a report of how many hours my employees do per week and it seperate them in different categories.

What I would like to do is find a way to match the cell's description with the amount of total hours that were spend on certain project.

So here is the scenario.

In my department there 6 employees that are assigned to work on certain projects in daily basis. These projects are called CRs and to identify them I've added a number after them.
So we will have CR0001, CR0002, ect. These are unique projects.More that one employee that could woork at the same project as well on other sections but I am only interested on the projects.

To help you understand what I am trying to do I will give you an example.

John is working on project CR0005, CR0006, CR0001. He has been working on them three projects for the last week.

Here is the summary of the hours:

- 5 hours on CR0005
- 10 hours on CR0006
- 5 hours on CR0001

This information is inputed in Excel spreadsheet Week 1 .....

View 12 Replies View Related

Employee Retirement Plan

Oct 27, 2007

how to use formula in the monthly contribution field to calculate 7% of pay (salary)

Lenght of Total pay Monthly contribution cummulative
Service retirement value
3.6 $528.94

Please one more thing. How do i use FV Function, monthly contribution, and the lenght of service field to to determine the current value of the employees retirement palan.

View 10 Replies View Related

Employee Timesheet With Overtime

Nov 2, 2008

i am creating a weekly time sheet for my company.the problem that i have is when the persons time reaches 40 hours, the time needs to be calculated in the overtime field. this is really tough for me when the person reaches 40 hours in the middle of the work day. I cant figure it out. i have attached the spreadsheet if you would like to look.

View 2 Replies View Related

Weekly Employee Schedule

Sep 16, 2008

I'm trying to create a weekly employee schedule using Excel 2007 and have several issues but will start with one at a time till we get them all resolved.

I put in my start hour '6' in cell 'B7' and get this: 1/5/1900 12:00:00 AM.

I would like for to display 6AM.

I figure if I get his cell fomatted that I can get the other times correct then go on to the next issue.

View 10 Replies View Related

Tracking My Employee's History Of Changes.

Apr 1, 2009

I am using excel as point of sale book (to record customer name, service, and total price etc.) at our hair salon. We have employees that may be there to manage alone from time to time, and enter clients into excel.
Our worry is straight forward, and involves them erasing what they wrote. I am confident that the actual service and price is entered at the beginning, but want to track their changes to their own entries.

The "track changes" would work if it "tracked changes" after entry. But it seems to track the last change from saving. For example....the employee enters $40.25 presses enter--after she knows she can get away with a change, she may erase it altogether or change it to say $16.75.

View 9 Replies View Related

Employee Scheduling Using Solver

Sep 26, 2006

I have a scheduling problem that I'm trying to get my head around. Im sure that some kind person can give me some ideas on how to get this started. It may be extremely easy, but I just cant get it off the ground. Basically I have 15 doctors that work for me - 9 full time (38hrs/week) and 6 part time (15+hrs/week) in a general practice. We are open 7 days a week

Open Close
Monday8:3019:00
Tuesday8:3019:00
Wed8:3019:00
Thur8:3021:00
Fri8:3019:00
Sat9:0018:00
Sun9:0018:00

Doctors have 5 days on and then 2 days off.

The below is the proposed daily schedule and the doctor requirements. % of booked patients for the times and % of random patients for the times are included as well as average waiting times.
Booked Random Waiting Doctors
8:30 - 10:00Morning Hours55%45%30min5-7
10:00 - 13:00Increasing80%20%45min7-8
13:00 - 18:00Busy 100%01.5hr8+
18:00 - 19:00Decreasing70%30%1hr6-7
19:00 - 21:00Night 40%60%30min5+

There are 3 types of patient visits
ShortDoubleTriple
%0.810.140.05
Minutes153045

58% of patients choose their doctor and 42% come in off the street and will take the first available doctor.

and finally - doctors cant work for more than 4 hours without a break. brek details are below
HoursBreak
<4hrs0
4 - 5hrs10min
5 - 8hrs2*10 mins - 45min lunch
8+2 * 10min 2*45min lunch

I need to create a timetable for the doctors which optimises their time based on the above details. I also need to design a data sheet for reception which will maximise the number of patients seen. Finally I need to calculate what the maximum number of patients the centre can see on an average day say monday.

View 9 Replies View Related

Calculate Each Employee Work Hours

Apr 11, 2013

Just like if I put the employee's number and work time for each day,

The excel will accumulate the hours automatically somewhere in the sheet. (I don't want the total hour cover each day's work time)

Is there anyway I can do it?

View 5 Replies View Related

Find Courses That Employee Has Not Taken From A List?

Dec 10, 2013

looking to find a formula to find the courses that an employee has not taken from a list. I have a list of courses in column F, in which I can compare to column C using their EmpID number from column A and obtain a result as taken or missing in column D. I am currently using :

[Code].....

or

[Code] ....

to get the results in column D. However, with these two formulas I am getting all the courses taken by each employee and not the missing courses.

View 8 Replies View Related

Formula Not Reading When Employee Shift Changes

Jan 24, 2014

I need a formula that will be able to show what shift someone is on if the shift changes, for example. if one of my employees shift changes from and 8hr to a 10 hr on january 16th 2014 then I would like the raw sheet to show the old shift, the new shift and when the new shift started (date). the index formula I have will only show one shift even if there are new dates with a different shift. the formula pulls from the sheet name editor.

i really want the shifts to be different if it was change on a certain date then from that date the employee shift will reflect new shift but still keep the previous shift prior to the day the new shift started.

I've attached a sample workbook to show.

MDT Report December 201322(FILEminimizer).xlsx

View 2 Replies View Related

Count Employee For Each Week Ending?

Jan 29, 2014

I have data that is added to every week. I need an equation to count how many times each employee show up each weekending.

View 3 Replies View Related

Excel 2003 :: Log Out Duration Per Employee

Feb 27, 2014

I have a Log In/Log Out report that I need pulling data from.

Ultimately what I want to do is get the amount of time my employees were logged out.

I am using Excel 2003. Attached is an example of the data I'm working with.

Sheet 1 contains the data, and Sheet 2 will be the summary.

So, looking at Bob's times: If column A contains Bob (A2 in this case), and the same row in column G (G2) contains 79, subtract the contents of the same row in column F (F2) from the next row down in column D (D3) (which would be D3 - F2). That result should populate on Sheet 2 next to Bob's name in h:mm:ss format.

If there is nothing in the next row down in column D, then do nothing.

I would like to leave the data as is, if possible. There are extension numbers next to the names in the data. If possible to ignore those when rolling up to the summary that would be useful. Also, on the summary sheet, if the employee is not on the data sheet (if they had the day off) they can have 0 or NA or something to indicate there is no data for them.

View 1 Replies View Related

Auto Update Employee Availability?

Jul 5, 2014

I modified an employee schedule that I found on here to meet my needs but I am needing one more thing:

I need a field (or separate worksheet in the workbook) where I can enter employee availability and if they are unavailable for a specific day, it will auto update in the calendar stating they are unavailable for that day.

View 7 Replies View Related

Employee Shift Roster Template

Nov 19, 2008

Any non-commercial roster template that would suit 7 day/24 hour/4 person shifts?

Currently we use a totally manual spreadsheet which looks like a Rubik's Cube and does not offer any form of automation in order to speed up entry adn ensure no doubling-up or missing shifts.

View 9 Replies View Related

Verifying Employee Shift Coverage

Oct 6, 2009

I would like to create a formula that would verify that specific work shifts have been covered each day. The spreadsheet has already been created by someone else, so I am hoping not to recreate the wheel.

The goal is to make sure that all desired shifts are covered with a result in the last cell of the column that would indicate "covered", "not covered" or even a true or false statement.
As an example, an 8am shift is needed. The choices are 8A or 8ALEAD and only one of these is needed for each day.

Can something be set up with conditional formatting from a master list of required shifts.
such as: 7OR, 730*(for just 730 and 730LEAD), 11A, etc.

This is just a snippet of what the schedule looks like: ...

View 14 Replies View Related

24X7x365 Employee Shift Scheduling?

May 4, 2013

I'm trying to come up with an Employee Shift Scheduling template. I have 6 employees total with 3 different shifts (day, swing, graveyard). I would like to not do a rotating shift. I'm trying to figure out if a 5X8 shift is better or 4X10 and I would like to come up with a weekly schedule where I can see what shift everyone is working and were I have double coverage.

View 1 Replies View Related

Count With Criteria For A Specific Employee

Jul 7, 2009

how can I sum up all the "Type of Defect"[CR, LOF, DR] for a specific Welder. They were tag as Welder 1-7. In the attached example (Sheet1- Data), Welder 1 has 2 "CR" and 1 "LOF", this figures to be reflected in Sheet 2 (TOD).

View 2 Replies View Related

Auto-fill Data Of Employee?

Jul 8, 2013

i've got on sheet "employee" in cel A - ID of employee and in cel B emlpoyee first and last name...now i would like to have in OTHER sheet "monthhours" when i enter ID of employe in cell A that in cell B excel automatically enter first and last name of employee that have got that ID...

A B
ID
FIRST LAST NAME

1
NAME

2
NAME

3
NAME

[code].....

View 9 Replies View Related

Summing Multiple Sheets Per Employee

Apr 24, 2008

I have 20 sheets set up exactly the same with employee payroll and another sheet that lists all of the unique names and id's. I need to get a total sum of all the sheets per employee. Please note that some employees can appear multiple times on a sheet.

******** ******************** ************************************************************************>Microsoft Excel - Book2___Running: 12.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutA1=ABCDEFG1last namefirst namePositionIDGenderDeptamount2JonesJohnX89891MF410$1,830.003JohnsonMikeX89892MD167$2,220.004AllenRayY89893MF410$675.005ThompsonDavidY89894MG43A$300.006JonesAlZ89895MHYJ$195.007PhillipsTomC89896MT567$150.008SmithBenV89897M67L$240.00Sheet1 [HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name boxPLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

View 9 Replies View Related

Subtotal Non-Unique Items By Employee

Nov 28, 2008

I have a list of sales associates by items by manufacturer. I need to count how many manufacturers are represented by each associate. It might look something like this:

Smith, John Brand A Beer 12 oz Beer Industries, Inc.
Smith, John Brand A Beer 8 oz Beer Industries, Inc.
Smith, John Brand B Vodka 700ml Vodka Industries
Jacobs, Sally Brand C Wine 1989 Wine Makers Co
Jacobs, Sally Brand C Wine 1979 Wine Makers, Inc.
Jacobs, Sally Brand D Wine 1908 Old Wine Makers, Ltd.
Jacobs, Sally Brand E Beer Six Pack Fun Beer Co.

So the totals would be:

Smith, John Total # of Manufacturers: 2
Jacobs, Sally Total # of Manufacturers: 3

So the formula ignores the duplication in employee names and manufacturer names.

Is this possible? I'm thinking something like =sumproduct((Range=AssociateName)*(sum(1/countif(ManufacturerRange, ManufacturerRange)))

View 9 Replies View Related

Spreadsheet Protection Based On Employee ID

May 12, 2006

I have a productivity report that is posted on an intranet. I only want the members of my deptartment to see the productivity results. To accomplish this, I need help creating a VBA so the names in column B will only populate if an employee enters his/her 6 digit employee ID correctly in cell B1. If the ID entered is not listed in column B of the "Rep ID Master" sheet, then an error message should pop up stating something similiar to, "Incorrect ID. Please Enter Your 6 Digit Employee ID". Also, Since I don't want the entire company to see our dept's productivity numbers, I want the VBA to prevent the associate from saving changes to the sheet, but still allow me to do so--preferably via a password.

View 9 Replies View Related

Employee Turnover Ratio Report

Jul 13, 2006

Need formula to calculate employee turnover ratio and if there is a place to get a free template to download?

View 9 Replies View Related

Age Employee Birthdates In 5 Year Increments

Feb 7, 2007

My employer gave me a life insurance "Rate per Age" chart to convert to an Excel spreadsheet.
The chart shows rate increases in 5 year increments starting at age <29 and increasing in steps as follows: 30-34, 35-39 through 95-99.

If I input an employee's birthdate I am hoping to calculate the date when he/she will hit each 5 year step increase.

View 9 Replies View Related

Employee Head Count By Month

May 8, 2008

I want to create a headcount per month for a monthly staffing spreadsheet.

I have an employee dept (A1), start date (B2), and end date (C2), and also record amount paid each month (D2, E2, F2).....

View 9 Replies View Related

ADVERTISEMENT