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 RepliesPlease 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 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.
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 RelatedAttached 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 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
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'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 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 have a set of data for hours of work. I want to make a simple Pie Chart: % of Hours worked overtime, and % of work not overtime.
The Data Looks something like this:
Name,Normal Hours Worked, Overtime Worked
David,8.0,3.2
Anne,8.0,1.3
Danny,7.0,0.0
Harry,8.0,3.0
Edward,8.0,2.3
So I want a pie chart that has 2 wedges.
~25% labeled as overtime worked
~75% labeled as normal hours worked
Whenever I put these values in the pivot table and use SUM, it is trying to sum based on # of hours matching, instead of by the column Name... so it gets divided into a bunch of different pie wedges.
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 am using Excel 2003.I need two distinct sums:
I need to calculate out the loan amount of approved loans by week (weekending on Sunday). For example for the week ending December 28th, 2013
$1,234,567,890 in total loans
I also need to have a list of sums of the loan amounts per code types of approved loans. For the week ending December 28th, 2013
$123,456.00 in total loans for Code: LTV/TLTV > Guideline max
$987,654.00 in total loans for Code: FICO > 10 pts below Guideline min
I need to ignore any loan amounts and codes for any loans that have not yet had a approved date entered and also ignore all loan amounts for any loans that are denied. I have tried doing this in many different ways with Pivot Tables and I am not getting the results I need.
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?
I am trying to calculate mandays starting from 1st December 2012 to 29th March 2013, and i want to count the days excluding the weekends, formula, i am trying NETWORKDAYS, i use MS2007.
View 2 Replies View RelatedI have built a model which has approx 33,000 different combinations (output) based on user selection. There are 15 different options for user selection which gives rise to the number of outputs (2^15 -1) = 32,767.
I have inbuilt a data table that gives the output for all of the 32,767 scenarios. However, it seems like for each change the calculations takes forever and I am not sure when the calculations are complete. Saving takes forever and stuffs up excel. The numbers keep changing in front of my eyes. The file is 10Mb big/small.
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.
production01.png
I am trying to figure a way to search for a cell that has a specific date and time range. There are several cell titles pending on the activity. I want to find a cell that has a time ** 7:30-15:30 , 15:31-17:30, 17:31-20:30. The end result is to calculate the activity between those time periods based on the data cells.
Example
If the date searched time field ** the activity ranges is 1635 I need to split the time and credit the activity time in the 730-1530 time and the rest on the 15:31-17:30 time
I have been able to do it on a single labor group based on time alone, but when I try to add the DATE to it my numbers go null. Eventually i will need to add 11 labor groups daily for weeks at a time .
Excel 2007
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 time column (A) that when looked in the cell only shows AM & PM times, but the cell itself (not showing) contains dates too, keeping me from be able to do a sheet wide sort of time or time frame occurrences.
Can I do some thing to sort these cells with their corresponding rows based on time only disregarding dates?
I am trying sort out all rows that in column (A) is time equal to or greater than 4:00 PM OR even maybe sort all rows that column (A) shows a time between 4:00 PM & 7:00 PM. The date in the cell is the problem, I think. Excel 2013
I'm working on converting some databases. One has entries with normal human readable time format, the other uses the unix epoch time format.
Is there a function or vba code that I can use in excel to convert the normal time format to epoch time?
I've got a thousand or so entries, so it would be nice to find a way to do this on a large scale.
Deciding to try and get to grips with Excel for basic accounting, I'd just like to check some things before I start filling columns... Say in column D I have a list of names, and in column E I have a list of figures: John Smith £250 Harry Davis £350 John Smith £500 What would be the formula for finding all occurrences of John Smith, and adding up John Smith's figures to give a total? In the simple case above, the answer would be £750. Would it matter if there are any empty/blank rows in the list?
View 9 Replies View RelatedI have a list of tenants.
Column A is the building number
Column B is the Area in metres squared
Column C is the tenant name
What I want to do is sum the vacancy (in sqm) for each building.
Ie, look at column A and choose the rows relating to a particular building, whose number is in column D1
Then look just at those rows and select those tenants named as "vacant"
Then add the areas from column B of those vacant plots.
In basic terms I have column A containing a list of dates, starting from 01/01/2005 and increasing by 1 day for each row, In column B I have the value for the day. These dates and values are still being used so the number of rows will increase day by day. I would like a formula to tell me the average total for January. So it would need to SUM each January before giving the average. I realise I could prob do this with a pivot table but if someone could give me a hint for a formula that would be great.
View 2 Replies View RelatedI have a number in a cell, lets say its 900. I want to multiply this number by 12 and then divide that by 52.
So a calculator I would simply type in 900 x 12 / 52 = 207.7
On excel I tried:
=sum(a1*12) /12
and it didn't work....
I am trying to create a macro that will sum the total number of 1's 2's 3's 4's 5's '6s in a range of cells d17:100 and return the number of 1s to cell a3 and number of two's to cell a4 and number of 3s to cell a5 and so forth.
I also need this to run each time any changes to any cell on that particular worksheet is made - sort of like 'refresh all the sums' type of thing.
I have been working on this spreadsheet for weeks and can't get past this part!