# 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

## 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

## 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.

## Time Calculation - Calculate Total Cost Per Job Per Person?

Dec 22, 2013

I am attempting to create a time chart that keep track how long a particular person works on an individual project to finally calculate profit & loss. Attached is the spreadsheet.

I am using invoice #'s to differentiate the jobs but have no clue how to make it calculate how much time was spent per job, per person then calculate the total cost per job per person?

sheets.time chart.xlsx

2014 tab- grey column is installer and white is shop employees
profit loss tab- all calculations done here
payroll tab- all cost data here

## 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.

## 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.

## 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.

## 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.

## 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.

## 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.

## 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] .......

## 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.

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

## 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.

## 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.

## 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?

## 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â€Ž

## Iteration Inconsistency: Allow For A Cost Being Added To Loan Amount Where The Cost Is Based On The Total Loan Amount

Mar 15, 2007

In a financial environment we have a calculator which uses iteration to allow for a cost being added to loan amount where the cost is based on the total loan amount. Iteration is set to 100 iterations with max change .001

On one PC the first time the calculator is opened it gives a particular (incorrect) result. If the input cells are cleared and the data re-entered, it gives the correct result. This only happens on one particular PC. Is there some other setting , other than the iteration setting, that would cause this?

## Calculating Cost Per Second

Oct 25, 2007

I'm trying to make a worksheet where I can calculate the cost of a mobile postpaid subscription. It is charged per minute and the cost differs depending on which of the 2 available networks the customer is calling to. The first 20 minutes are free, not depending on network.

Edit:
Charges to network A is 1,79,- per minute after the first 20 minutes are spent.
Charges to network B is 2,29,- per minute after the first 20 minutes are spent.

To sum up:
1. The customer makes a call.
2. If there there are available free minutes, these should be spent first.
3. The customer is charged per minute, depending on network called.

## Calculating Cost

Apr 17, 2009

Problem - billing spreadsheet for prisoner fee.

1 - 8 hrs = \$55
9 - 24 hrs = \$55 + \$65 or \$120
Anything over 24 hrs - \$65 for each additional (24 hrs) (\$185)

So if you were locked up for 6 hrs it is \$55. If you were locked up for 18 hrs it is \$120. If you were locked up for 28 hrs it is \$185. And if you were locked up for 49 hrs it is \$250. Cell F5 contains number of hours locked up - I would like cell I5 to calculate the cost of the stay. I am proud of myself for figuring out the date and time subtraction - but this part has me stumped.

## Cost Per Row Of Data

Mar 6, 2014

I have an excel table that among other fields it has a [product Charge] field.. what im trying to do is to get a cost per row based on the [Product Charge] field.. so example if I have 10,000 rows and the total for that field is 1,000,000 the cost should be 100 ( [Product Charge] / total rows).... I then want to be able to use it in a pivot table...

## Least Cost Routing

Aug 3, 2009

im working on a VoIP company, and having a hard time to figure out a result on a little time.

the problem is finding a "least cost routing" wherein you will find the cheaper to higher price, and putting the results on a designated columns:

I am including an image, sorry if the image seems to be heavy and large:

as you can see: Column C contains the sale rate for the client, while Columns D to I, are the buy rates from our source carriers.

While the columns K to P, are the columns where should one source carrier should be fitted base on the buy rates, that is best fitted to the sale rate.

Red marks=are not suitable or non-profitable buy rate for the sale rate.

Sorry for the bad english, but i hope you get what i mean. this is only a sample from our Internal data.

## Cost Vs Units Graph

Jan 25, 2009

I'm wondering how to display the following cost model on a graph (this is a piecewise-linear cost model for some production company):

Cost per unit for 0-10 units: \$1
Cost per unit for 11-20 units: \$3
Cost per unit for 21-30 units: \$5
Cost per unit for 31-40 units: \$8
Fixed cost: \$100 (this is regardless of the number of units produced)

Basically I'm looking to construct a cost vs units graph.

## Calculating Cost Per Month?

May 8, 2014

I am trying to create vba macro that allows me to calculate cost per month.

I have created a basic table with two columns

Cost
Month

200
Jan

300
March

150
Jan

470
April

150
March

I have another sheet , that has two columns

Month - Cost
Jan
Feb
March
April

I am trying to sum up the value on sheet1 and input into sheet2 on the allocated month.

So far logic , that I have came up with is.

I create a For loop statement that looks column and sort each month into an array.

Than the second loop will use the array to find the cost with the allocated array value to sum it.

## Transferring Cost From One Sheet To Another

Feb 24, 2009

I am trying to figure out how to update cost from a manufacturers price sheet in excel. I have one worksheet that has the manufacturer's part number and my current cost. A=item number B= current cost. on the second sheet is the same setup but my price list has roughly 3500 rows and the vendor price sheet is 23000 rows. Is there a way to automate the cost replacement by comparing item number and if they are the same replacing cost on sheet one with cost on sheet two?

## Cost Based Mark Up

Sep 26, 2007

set up a cost based markup spreadsheet?

I need to be able to mark up values from 0.01-9.99 by 20% of their value, and 10.00 and above by 10% of their value.

I only know how to mark up values by a single percentage.

## Simple Job Cost Sheet

Feb 20, 2009

The job cost controller is not exactly what I need, so I have been trying to create my own. So far my progress has got me to be able to separate each laborer for each job.

Here is my query: I have a drop down menu that lists ten different tasks(i.e. carpentry, painting, demo, etc.) and I want each of the ten tasks to separate themselves. Is this a vlookup function? How do I make excel listen?

## Cost Average Effect

Oct 19, 2006

Example number 1:
You invested 10.000 Dollar in the year 1985 into a tempelton fund and waited 20 years. Result can be seen at many forums and stock plattforms

Example number 2:
In 1985 you had no 10.000 Dollar, but only 100 Dollar a month. You invested this 100 Dollar each month for 20 years.

How big will the difference be between example 1 and 2 ?

*** Background.
If a "share" will cost 100 Dollar, you will get 1 share for 100 Dollar. Next month share is down to 50 Dollar, you will get 2 shares for your monthly 100 Dollar. Next month share is up at 200 Dollar, you will get only a half share for your 100 Dollar. Its called cost average effect.

Tool should allow "play arround", to simulate various scenarios, talking into account growth rate, time period, monthly investment, fluctuation of a theoretical share. Idealy we would love to " import" e.g. famous fund history (eg. Tempelton, Pioneer) etc. to see how investment would have developed when a steady monthly payment would have been made.

## Calculate X Percentage Of Cost

Apr 15, 2008

I am trying to create a simple formula to extract cost from a total that includes both cost and and a percentage for maintanance. Assume \$100, 10% of which is maintenance the remainder is cost. If I just subtract 10% from \$100 I get \$90, however 10% of \$90 is \$9 which equals \$99.

## SUM Based On Cost Center Wise

Jan 14, 2014

I need a sum of amount based on the Cost center & Category from the details table , can i have a formula to get them .

Please find the example amount in the attached excel sheet highlighted in RED Colour.