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")

ADVERTISEMENT

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

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

Employee Schedule Auto Complete In New Worksheet

Sep 2, 2006

This should be really easy, but for some reason I can't figure out how to make this work. I'm managing a resteraunt, and build my schedule by department using a drop down menu to insert my employee. I'd like to be able to automatically resort the data into a new worksheet organized by employee name. please see attachment.

View 9 Replies View Related

Work Schedule: Insert The Numbers 1-6 Into The Spreadsheet Throughout The 14 Days For Each Employee

Oct 7, 2009

I developed a 14-day work schedule and I assigned each different job position a number. The different job positions are numbered 1-6 and are as follows: #1=5:30am-1:30pm, #2=6:00am-2:00pm, #3=9:30am-1:30pm, #4=12:00pm-8:00pm, #5=1:00pm-8:00pm & #6=6:00pm-8:00pm. Numbers 1,2,4,5 clock-out for a 30 minute lunch break, while the other numbers do not.

My goal is to insert the numbers 1-6 into the spreadsheet throughout the 14 days for each employee, and have Excel calculate the total number of hours for each employee in the far right column. I would also like "V" & "H" to equal 7.5 hours. This would save a lot of time instead of going through and adding up the hours with a calculator

View 3 Replies View Related

What To Keep Track Of Employee Project Hours To Then Do Some Calculations With Totals

May 20, 2014

I have been working on the attached spreadsheet, we have projects that our employees work on. I need to keep track of the hours spent on each. Then I need to combine this information so I can do some calculations, for example based on the total hours spent I can then work out the actual cost spend on the project versus what we have invoiced for this project, like a mini Profit and Loss on the labor only.

I created a different worksheet per employee, I would like each employee to only be able to access there own worksheet, based on some protect or share worksheet rules. I created lists of Projects and Users, so this data is the same in each worksheet. Then I would like the data from each employee (only have 3 at this stage) to collate onto another worksheet, which only I can see, so I can do my total calculations etc. Along with hours, sometimes each project might have extras, this is charged at a certain rate per employee, so on the totals page I will also use this information.

Here is what I have so far. Workings 150514 - sample for expert.xlsx

View 1 Replies View Related

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]

View 9 Replies View Related

Time Sheet Schedule

Nov 21, 2009

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)

View 6 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

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

Calculating Overtime And Regular Time Based On Shift Schedule?

Nov 19, 2012

I am trying to calculate over time based on shift time.

For example: Regular shifts are between 7:00 AM to 3:00 PM (Monday thru Friday). Anything between those hours and on those days should be considered REGULAR TIME. Anything between 12:00 AM to 6:59 AM or between 3:01 PM to 11:59 PM should be calculated as OVERTIME.

Anything on Saturday or Sunday should be calculated as OVERTIME as well.

View 14 Replies View Related

Time Calculations.?

Feb 9, 2010

if you look at attachment, in Cell J2, if cell I2 is greater than 2 minutes but less than two minutes, the I need the value of I2 in Cell J2 otherwise 0:00:00 then K2 between 3 & 4 Mins and so on.

View 5 Replies View Related

Time Sheet Calculations

Feb 6, 2010

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.

View 2 Replies View Related

Time And Cost Calculations ..

Aug 14, 2006

I am compiling a simple worksheet that will keep an ongoing track of labour costs in a production environment. The objective is to end up with a labor cost "per unit" for packing punnets of soft fruit.

The source data I have is;

1 - Start time and end time of the job

2 - Any breaks taken during the job

3 - The number of staff it took to do it

4 - The status of the staff (Supervisor, temp, etc) and their hourly pay rates

5 - The number of punnets packed.

With all of the above it should be a relatively simple exercise to calculate the cost per unit (and with a calculator is!). My problem appears to be that I am not formatting something correctly, because when I try to calculate the costs for the employees' my costs are obviously wrong. I have attached my early draft for reference, in the example shown I am showing that a supervisor earning £7.50 an hour worked for 1 hour 15 mins at a cost of £0.39

View 2 Replies View Related

Multiple Time Calculations With Breaks

Jul 17, 2014

I have a start time and a stop time, then a difference is calculated in cell L53. I want to subtract the time value of a break but it only works if the entered value is greater than 1:00. I tried K2-I2-L55 and It works but It wont work for times less than 1:00. I need to be able to subtract :15, :30 or any other value in cell L53.

View 4 Replies View Related

Time Calculations Subtraction And Average?

Dec 10, 2013

1st problem concerns entering data as text and it being converted to time. I have found the formula 00:00 which does an excellant job of converting. However, when I want to subtract, (=a2-a1) for example, it treats the time as a number. Sometimes it works, but if the hours are different, say 23:30- 22:10, it will subtract 2330-2210! Is there a way to make this work?

2nd problem, related to the first is when I try to avg a column of times, I get a similar effect.

View 1 Replies View Related

Time Calculations - Multiple Timestamps

Feb 12, 2014

I am trying to track the time the various activities take. I need this in hours:minutes - Monday through Friday. Attached is the spreadsheet that does NOT work. An "x" in the column creates the timestamp in the adjacent column. Some of my formulas are not correct as the calculations do not create the correct data.

View 1 Replies View Related

Excel Time Calculations Sums?

Nov 28, 2013

i have a tab on the bottom that is labeled "Man hours 2013" i need to pull the information from this sheet and create a new tab that will pull the property name, how many weeks of service, and the total man hours for each property. i will then be adding a column were i enter the monthly price for this contract divided by the number of weeks we serviced to figure out our percentage of gain/loss.

View 11 Replies View Related

Userform Textbox Calculations (time)

Feb 21, 2012

I've got a userform which I'm developing (my first) and I have two textboses:

Textbox6 = start time & Textbox7 = end time & Textbox10 which contains the calculation (Textbox7 - Textbox6).

Now I have code that works great for normal numbers however I need to be able to make the calculations in TIME (hence the start time / end time).

How I can amend this code to be able to calculate total time between textbox 7 and Textbox6.

Private Sub TextBox6_Change()

If TextBox6.Value = "" Then Exit Sub
If TextBox7.Value = "" Then Exit Sub
TextBox10.Value = CDbl(TextBox6.Value) - CDbl(TextBox7.Value)

[Code] .......

View 3 Replies View Related

Time Calculations - Getting Hours In Minutes

Feb 27, 2012

If I want to make a formula for a time such as

I have this time like 4:30 hour and i want to get this time by minutes like 270 minutes.

View 1 Replies View Related

Time And Cost Calculations Per Minute

Oct 17, 2007

I have costs per minute and call times in seconds

How do I work out the cost per call in Excel?

It's probably something very simple, but I just can't work it out. No matter what I try, I can't get it right

View 9 Replies View Related

Weekly Time Sheet Calculations

Sep 22, 2004

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.

View 9 Replies View Related

Rota Time Calculations And Colour Coding

Sep 13, 2013

I am currently working on a rota for my work. What I would like to code is that if a shift begins before 8am and finishes after 2pm it automatically deducts an hour for a lunch break. The spreadsheet currently calculates how long a shift is excluding any calculations for breaks, then checks whether that shift is a normal working day for the staff member, and returns the additional hours the staff member worked on that day. Separately, if possible I would like the spreadsheet to colour code each cell. So if a shift starts before 9am the cell fill color to be red, and if a shift begins after 1:30pm for the cell fill color to be dark red.

View 4 Replies View Related

Time Efficient Methods For Calculations Based On Different Set Of Data?

May 27, 2014

how to calculate averages and standard deviations based on different time periods without having to manually change the cells?

example:
1st average output at z3, 1st std dev output at z4
data to calculate from c3:c50

2nd average output at z5, 2nd std dev output at z6
data to calculate from c51:c98

3rd average output at z7, 3rd std dev output at z8
data to calculate from c99:c148

and it goes on based on this sequence. i would like to know how to do this without having to change the cells each time i want to calculate. basically what is the quickest way to calculate following this sequence?

View 6 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 2007 :: Time Difference Calculations And Automatic Insertion Of Text In Blank Column

Jan 4, 2014

Please find attached a daily copy of a spreadsheet that is used to monitor train running times.

The columns on the left - "Serv No, Serv Start Date,Train No:, Scheduled Arrival, Scheduled Depart" are provided to us automatically and the underlying cell formatting/formula cannot be changed. This information is cut directly from a daily report sent to us the previous day. The cell formatting for the start date is custom - dd-mm-yyyy hh:mm.

The sheet is usually locked out with user access only to the left hand side to cut and paste train times in and the input columns on the right hand side. User has no requirement to adjust any details in the left had column apart from to add additional trains at the bottom is need be. I have left the loaded sheet blank and complete with all underlying cell formatting, conditional formatting and formulas intact for you to play with.

Both sheets essentially do the same thing so whatever formula changes are made to the empty sheet can be adapted for the loaded sheet.

For simplicity, I will focus on the empty sheet. What I am mucking around with is having the user input the train arrival time in the "Actual Arrival" column - (column F)

What I am looking at then making occur is the spreadsheet automatically filling the adjacent columns with the appropriate text and calculations.

To do this excel needs to compare column the number (cell input is text formatted) as a number with the arrival time in column G. It then needs to calculate if the train is Early (16 minutes or more before scheduled arrival), on time (+/- 15 minutes either side of scheduled arrival) or late (16 minutes or more after scheduled). It then needs to automatically place the letter "E", "O" or "L" in column K and the time differential in column L.

My problem is two fold.

Firstly, I cannot seem to make the spreadsheet automatically enter a letter into column K depending on the above conditions.

Secondly, I have had limited success in having the spreadsheet compare column J with column F and working out the differential. However, this only works if the time remains linear. IE only if the train runs on time or late. It gives an incorrect time differential if the train is early. I will attach this spreadsheet tomorrow as an add on to this post.

DAILY TRAIN RUNNING.xlsx‎

View 3 Replies View Related

Changing Date/time To Run A 12 Hour Production Schedule, And Not 24 Hour

Sep 4, 2006

I have created a daily schedule which has a number of factory variables taken into consideration which determine the date and time a particular product should, barring any mechanical problems, come off the machine. (see attached spreadsheet).

The date at the top will be editable by me only so that when I update the production quantities, the “date/time off” column automatically re-adjusts to the remaining quantities.

The formulas are a little long winded, but I have left them that way whilst I try and develop it. I should be able to figure out how to condense them later.

My problem is that the “date/time off” on the right works excellent, but over a 24 hr period.

Ordinarily, we work a 12 hour day (6am to 6pm) with overlapping shifts to cover breaks, and 20 mins warm up at the start of the day for the machine, thus maximising a 12 hour day.

Of course if demand exceeds the allotted time we put on overtime.

Is it possible to specify that normal days are only 12 hours so that if a product exceeds 6pm, it flows into the next day with the balance starting at 6:20am?

And, if the production for the week exceeds the time could I stipulate particular days which we deem are suitable for overtime? Ie, we decide Wednesday is a 14 hour day and not 12.

I had toyed with the idea of creating a 365 day table/calendar, on another worksheet which would have its individual allocated hours in an adjacent column and somehow link them to the date/time off, perhaps by way of a VLOOKUP, but I have been chasing my tail trying to figure out how to implement it.

View 9 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

Reset The Schedule..

Jan 15, 2009

Is there a way to take the values between C2 & D2 and have them automatically post in column F ?

View 2 Replies View Related

Schedule, Organizing

Oct 15, 2008

I make our employee schedule at work, and so in Excel I have eight sheets for one workbook. (Employee schedule, then each day of the week) On each day is a table separating the shifts. Morning, Split and Night.

I was wondering if there was a way for Excel to look at the schedule sheet, and then automatically place each employee and their shift on the following day sheets. (example: Joe has 2-10 on Monday, 10-6 on Thursday and Friday) So the Excel puts Joe underneath Split 2-10 for Monday, and Morning 10-6 on Thursday and Friday.

View 10 Replies View Related

Schedule A Macro To Run

Nov 15, 2008

I am trying to schedule a macro to run at 11:45 pm every time. I use system scheduler to open the excel file and when I use this command to run the macro nothing happens. The workbook opens fine but the macro does not run. I only wants to run the macro once day even if I open the workbook during the day I do not want it to run.

View 13 Replies View Related

Schedule Template

Oct 16, 2009

We have huge restrictions on hours, and to avoid inputting, then revising the schedule into my companies scheduling module, then having to change it again when the boss looks it over, it's just confusing.

Anyway, I'll get to the point, I want to add the hours up in a row of cells for the week sunday through saturday. Like so:

1100-2100 | 1100-2100 | OFF | 1100-2100 etc... I want it to add these cells hours up and have the total end up in the 8th cell.

Is this possible? I am a new excel user, or at least, setting up my own sheet.

View 10 Replies View Related

Scanning Schedule

Dec 2, 2006

The table posted above is the schedule for my employee. I need an input box which asks for a date. When the user supplies date, it should look for people who are scheduled for that particular date and who are on leave and put the result in the next worksheet at the last occupied row.

For instance, if I supply the date as 2nd Dec then the next worksheet should be filled with @ row # 2 (Row1 has headings)

EmployeeEmp IDSchedule
A123409:00 PM - 06:00 AM
B123511:30 PM - 08:30 AM
C123602:30 AM - 11:30 AM
E123811:30 PM - 08:30 AM
G124009:00 PM - 06:00 AM

Then if I select 3rd Dec, the next worksheet should be filled with the following data starting fom row number 7 (just below the above data)
A123409:00 PM - 06:00 AM
B123511:30 PM - 08:30 AM
D123705:30 AM - 03:00 PM
G124009:00 PM - 06:00 AM

View 9 Replies View Related

How To Write A Schedule

Feb 9, 2007

Excel is a new program for me sorry to say it but i have been try to figure out how to write a schedule on it and tally the hours for each employee i have them set up in rows right now and i hope i can keep it that way but i am tired of add hours up for each employee every week i'm sure it is really easy but can some one let me in no the little secret

View 9 Replies View Related

Weekly Schedule

Feb 24, 2009

Have a basic weekly schedule for 300 employees. Need to be able to identify the last day off in previous week so the following week isn't scheduled to work on the 6th or 7th day without a day off. For example, if schedule is Mon - Sun and employee #1 has Mon & Tues off this week, next week Mon would be the 6th day so I wouldn't schedule that person on Monday the following week. Calculate last column "Next Wk Mon" instead of manually figuring it out... Need to be able to identify last day off, count the days from that point to the next monday. It becomes difficult when days off are split...

MonTueWedThuFriSatSunNext Wk
23-Feb24-Feb25-Feb26-Feb27-Feb28-Feb1-MarMon
Emp 1OffOff6
Emp 2OffOff5
Emp 3OffOff4
Emp 4OffOff3
Emp 5OffOff2
Emp 6OffOff1
Emp 7OffOff1
Emp 8OffOff4
Emp 9OffOff2

View 9 Replies View Related

Schedule Tasks ..

Apr 6, 2009

Im having trouble with getting a macro to run at a specific time and day (Every Thursday at 7PM).

Here's what I have done so far..

1. I added my excel file to the task scheduler
2. created a class module and place the code below into it.

In testing the workbook opened but it asked if i wanted to enable or disable the macros - how do I get around this? And it does seem to kick off the macro (create_all).

Private Sub Workbook_Open()
If Hour(Now) = 1130 And Weekday(Now, vbMonday) < 7 Then
' TheNameOfYourCurrentProcedureHere
create_all
If Workbooks.Count = 1 Then
Me.Save
Application.Quit
Else
Me.Close True 'save the changes and close the workbook
End If
End If
End Sub

View 9 Replies View Related

Creating A Schedule

Jun 12, 2009

I am Cuttently creating a schedule in excel for work. the schedule will look like this:

Name.........................Shift
Andrew.......................6-3
Jonathon....................12-7
Kim............................9-5

etc etc

the thing is though this is simple enough but i am trying to use a bar chart to help me show how many people i will have on shift at anytime e.g ...

View 9 Replies View Related

ADVERTISEMENT