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.
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.
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.
I work at a call center and I have to report absent time for my agents.
I am working on a template that was previously saved, however, the absent hours do not account for when the person has not been to lunch yet. This is usually done as a manual update.
There are three main situations that I need my formula to account for:
1) Person that has been absent all day
2) Person that has been absent part of the day (after lunch)
3) Person htat has been absent only a few hours (before lunch)
Lunches are 30 minutes long
Whenever a person is absent or late, we put their time in two columns called ABS in and ABS out. It basically marks the start and end of their absent time.
ABS IN is when their schedule would have startedABS OUT is when they arrived if they were late, or their out time if they were absent all day.
Here is what my spreadsheet would kind of look like... I need to know how many absent hours there were to calculate the actual worked hours.
I cannot simply deduct abs out minus ABS in because then I am not factoring in the lunch times.
This is how my spreadsheet roughly looks like:
Agent
SCH TIME IN
Lunch
SCH TIME OUT
ABS TIME IN
ABS TIME OUT
Total Sch Hrs
ABS Hours
Actual Worked Hrs
Person absent all day
8:00
12:00
16:30
8:00
16:30
8.00
Arrived late (before lunch)
8:00
12:00
16:30
8:00
10:00
8.00
Arrived late (after lunch)
8:00
12:00
16:30
8:00
13:00
8.00
On Time
8:00
12:00
16:30
8.00
I am trying to get the start time and finish time for breaks.
I have 4 columns Name, status, start, finish. In the name column there are multiple names, under status there 6 different status's. and under the time columns there are in and out times dependant on status. I am trying to us index match match. I have tried vlookup and hlookup as a nested function. I keep getting errors.
NameStatus MessageStart TimeFinish Time
Cameron WilliamsTech All & Billing12:31:4812:31:52
=index(starttime,match(name,namefield(match,"on lunch",starttime)))
[Code]......
I am creating a worksheet where employees schedule work throughout the day
eg task 1 will be start time 9am, finish time 11am. The result is 2 hours to do the task.
The problem i'm having is that I need to do a calculation that if a task includes lunch then the result takes an hour away from the task time.
eg task 2 will be start time 12pm, finish time 3pm, excel displays 3 hours, i need it to say 2 hours because 1-2pm is contractual lunch.
I can't just do a day formula that says minus 1 from total as this is task orientated and will vary depending on start and finish times of each task.
I would like a solution for the automatic calculation of the end date and end time for project tasks. I have already spent hours on the issue, thanks for any help on this.
The parameters are:
A1 = Start time 08:00
B1 = End Time 17:00
A2 = break lunch 12:00
B2 = back from lunch 13:00
Task parameters
A5 = start date 01/03/10 (entered manually)
B5 = start time 10:00 (entered manually)
C5 = duration 02:00 (hrs entered manually)
D5 = "end date" >>> (to be calculated exluding breaks and holidays)
E5 = "end time" >>> (to be calculated exluding breaks and holidays)
the next line should be filled in automaitically according to the hours needed and the previous end date & time
A6 = "start date" >>> (after line 5: to be calculated exluding breaks and holidays)
B6 = "start time" (after line 5: to be calculated exluding breaks and holidays)
C6 = duration 14:00 (entered manually)
D6 = "end date" >>> (to be calculated exluding breaks and holidays)
E6 = "end time" >>> (to be calculated exluding breaks and holidays)
and so on for every new line down.
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 RelatedI 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
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] ...........
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 Related1st 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.
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 Relatedi 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 RelatedI'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] .......
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.
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
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")
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 RelatedI 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 Relatedhow 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?
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.
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?
View 2 Replies View RelatedPlease 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‎
I am working with the following time sheet/card (attached) and it works great when the person goes to lunch, but when they skip lunch I cant get it to compute correctly. I set the formula back to original state because it was just getting more and more confusing. Additionally I tried to adjust it for working overtime (more than 8 hours in a day) and double time (more than 12 hours in a day and kept getting errors or incorrect results).
View 9 Replies View Relatedthe vendor has a 21 hr working window; start from 7am and goes until 4am; Mon to Fri.
Here is a scenario:
- i request for a product information from a vendor on 3-Feb-14 8:00am (Monday)
- he replies with all of the product info on 6-Feb-14 12:00pm (Thursday)
can you find the time in above scenario consideration the working window?
Here is another scenario:
- i request for a product information from a vendor on 6-Feb-14 8:00am (Thursday)
- he replies with all of the product info on 11-Feb-14 12:00pm (Tuesday)
- Sat & Sun are days off but keep in mind that my Friday shift ends on sat at 4am so the networdays formula wont work.
I am trying to provide a tool for department leaders to monitor productivity for order processing in their departments. The variables I have are: Number of orders(variable), number of pickers (variable), start time(variable). Then, I know each order takes 1 picker 4 minutes to pick on average, and there are 45 minutes worth of breaks during the picking process. So after entering the variables I used =(((C3*4)/60)/D3) to come up with the time needed to process the orders. What I can't get to is how to add this number to the start time, factor in break minutes and get to the projected completion time. I have Excel 2003 at work. Clearly I need to take a class!
View 4 Replies View RelatedI am looking for a function to convert time given by my computer (Local time) in EST (Eastern Standard Time). We are several users of a same file (with timestamp macros) and all time need to be aligned to one time zone (EST), even if all users are working in different time zone (EST, CST and IST).
View 1 Replies View RelatedConsider this code:
'light eligibility
Dim facb As String
Dim sunset As Variant
[color=green]' check if facility has lights[color]
facb = WorksheetFunction.VLookup(RID, ds, 10, False) 'find facility code
If WorksheetFunction.VLookup(facb, fac, 6, False) = "Y" Then 'facility has lights
sunset = WorksheetFunction.VLookup(tempws.Range("A9"), sun, 2, False) 'lookup the sunset time based on the record's date
[Code] ......
This code checks the need for lights at a facility.
It first checks to see if the facility even has lights by cross-referencing a value in the record with a facilities database.
If it has lights, it then checks to see if they are needed. If the rental goes past the sunset time, then it needs lights. Sunset is determined by cross-referencing the date value in sheet1! A9, with the sunset database.
If it needs lights, variable lghtson is calculated equal to "sunset"-30 minutes.
As I step through this code:
WorksheetFunction.VLookup(facb, fac, 6, False) = "Y" Facility has lights.
Check to see if lights are needed.
sunset = WorksheetFunction.VLookup(tempws.Range("A9"), sun, 2, False)
sunset=0.879166666666667 which is 9:06PM. This is a proper value from the lookup.
If rental_end.value > sunset Then
rental_end (value from textbox) = "9:30 pm" , sunset=0.879166666666667. This is true, and Excel accepts it as true ...
lghtson = sunset - 0.5
0.379166666666667 = 0.879166666666667 - 0.5 (9:06 AM)
This is not the value I was looking for. I was looking for 8:34PM (0.856944444444444)
I am looking for a macros VBA where a user insert or update a data the date and time should be insert in column I and save the workbook.
Note: If the column I already have the date and time inserted before then it should give message record already have date and time.
I am using office 2010.