Working Time Formula Required
Aug 28, 2009
I have a fairly complicated working time calculation I need to make, the sheet calculates how much 'actual working time' is spent on a product. 'Actual working time' means just that, breaks & any other non working time removed from the total.
My sheet set up is as follows:
Column Z = Start Date
Column AA = Start Time
Column AB = Finish Date
Column AC = Finish Time
Column AD = Actual working Time (hh:mm)
Column AE = Planned stop time (hh:mm)
Standard working times are:
Monday to Thursday 07:30 to 16:15
Friday 0730 to 1200
First tea break is 10:00 until 10:10
Lunch is 12:30 to 13:00 (except friday)
Last tea break is 14:40 to 14:50
1) Jobs can be 'in production' for many days, sometimes weeks
2) Overtime may be run before or after shift, also weekends
3) We can have 'Planned stop time' which will also need subtracting
View 9 Replies
ADVERTISEMENT
Oct 7, 2006
d9 is where i enter my start time from a drop down menu and
d10 is where i enter my finish time
what i would like to do is have a formula to work out my total hours work then minu 45min and tell me whats is remainig which is over time
example
i start work and 06:00 and finish work at 15:00 which is 9hours i then remove my 45min break which then give me 1/4hour (0.25 of an hour) as over time
the 0.25 is then timesed by a figure of say 13.4481 which would then tell me that i have earnt 3.362025
and so on eg if i have 0.5hour it would tell me what that work out as.
View 14 Replies
View Related
Feb 24, 2014
1.The attached file shows an example extract of a data extract that has thousands of lines. See the Data Tab.
2.What I need is some time that has passed between two dates in a DDHHMM format
3.What I then need is the top 50 of each of the times (or the longest time past)
4.The column headers will be: WO Number | Contractor Name | Time Calculation – see the various Report tabs
5.The calculations in the Data tab are between the following columns.
a.Difference between Column Z and Column AC
b.Difference between Column AC and Column AD
c.Difference between Column AD and Column AE
d.Difference between Column AE and Column AF
6.Is it possible to show the top 50 only in time?
This report is forming a part of a larger report and all other formulas are already present so I am hoping to keep the file size quite small. The aim is to then hand this over to someone else to just print on a monthly basis depending on the data that gets added with minimum input.
View 2 Replies
View Related
Jul 10, 2012
I have the following code to check the date of birth entered into cell C18 and to alert the inputter if the age is either under 16 or over 25.
VB:
Sub AgeValidation()
Dim age As Integer
Dim dob As Range
Dim AgeMsgAnswer16 As String
Dim AgeMsgAnswer25 As String
[Code] .....
It works to some extent in that the correct message box pops up if the age is under 16 and similarly if over 25. If answering Yes on either message box then the correct thing happens. Its what happens if the inputter selects No thats not right. If the age is under 16 and the user selects No in answer to "Is this correct?" then the code is clearing the cell contents and showing the calendar again but is also popping up the 'Over 25' validation message box which then won't go away until Yes is selected. Also there are then multiple copies of the calendar open.
What I need the code to do is look at the date selected from a popup calendar in c18 and decide if that age is within the 16-25 year old range. If it is outside that then the inputter needs to be alerted to it. I can't use the inbuilt data validation because there are some scenarios where it would be acceptable to have an age outside of that range but we want to cover inputting errors as well as double checking the age.
When a msgbox pops up to alert the inputter and they choose "Yes" to say the date of birth is correct then I want the focus to go to cell C20 ready to input the next piece of information.
When the inputter selects "No" on the message box, then I want the original date to be deleted and the calendar to reappear so they can select another date. So effectively resetting the field so they can start again choosing a date like when they first entered the cell.
View 4 Replies
View Related
Jan 8, 2010
I keep on getting the "Run Time Error 424 object required" yet the code seems to look fine .....I'm asking for it to look for the date and have stated which cell/range ... I don't get it.
View 14 Replies
View Related
Aug 22, 2006
I have an Excel workbook that I have built in user-level security to log into. If login is successful I have code like this:
If 'successful conditions
Goto Success
'more code here that doesn't matter
Success:
'verfies that it is a new project
lgCreateProject = MsgBox("Would you like to create a new project?", vbYesNo)
'opens a userform if new project
If lgCreateProject = vbYes Then frmQuoteInfo.Show
Unload Me
End Sub
If the user signs on successfully and is not starting a new project then they get the error noted in the title of my thread.
View 9 Replies
View Related
Nov 7, 2006
Then the change event in the ComboBox of TB5 stopped working with the above error message.
Here's the
Private Sub TB5_Change()
'Loads TB6 thru TB16 from TB5's RowSource
i = 0
If Not TB5.ListIndex < 0 Then
For i = 1 To 12
If Not i = 1 Then Me.Controls("TB" & i + 4).Text = TB5.List(TB5.ListIndex, i - 1)
Next i
End If
End Sub
The segment that is erroriung out is:
Me.Controls("TB" & i + 4).Text = TB5.List(TB5.ListIndex, i - 1)
The first time it errored debug said there was a type mismatch. That only lasted about 15 min before it changed error message to the one in the title. Debug highlighted this same code segment for both.
TB5's original RowSource is "FoodList_2" which is 12 colums wide and variable number of rows. After picking the item from the first column this code fills in the rest of the data for the chosen row. I'm not sure how wide the rowsource is with the find code.
View 3 Replies
View Related
Jul 20, 2014
i want a VB/Macro Code which can do the following...
0 0
1 0
0 1
i just want to enter only the these "values" in the cell of G9 & H9 only and down below by dragging
View 9 Replies
View Related
Oct 3, 2006
I am running regression statistics for several different columns of data. In order to help me analyze the data, I am organizing the output on a specified worksheet called "Best Subset." I have stored the column names of the data used in different regression calls in a range array called Reg_Labels. The Reg_Labels array may contain one, two, or sixteen column labels in one specific location (where each column label is stored in a separate cell). All possible column names are stored in the Labels array (each name is stored in a separate location). I basically need to check to see which column names were present in a particular array location in Reg_Labels. When a column name from a cell in Reg_Labels(I-1) matches a column name from Labels(K), I am placing an "X" on the designated spreadsheet to indicate which variable(s) has been used.
My For..Each Loop will not work for some odd reason, and I need to figure out why. I keep getting an error that an object is required. I need each cell in Reg_Labels(I-1) compared with Labels(K), and I thought the For..each loop would be the easiest way to do it.
I just don't understand what's going on if C is a range object and Reg_Labels(I-1) is a range object. Wouldn't this loop supposedly cycle through every cell in Reg_Labels(I-1)?
Have I messed up some sort of object reference? ....
View 6 Replies
View Related
Apr 25, 2013
I am trying to copy formula as follows:
2
3
1
10
=A4*B4*D2
=A4*B4*D3
[Code] ......
I'm trying to copy the formula both down and across but I'm guessing both issues are the same.
View 3 Replies
View Related
Jan 14, 2009
I have a new sale structure to put in place the commission is paid in the following way:
below 1500 zero commission
between 1501 and 3000, commission at 16%
between 3001 and 8000, commission at 23%
above 8001, commission paid at 30%
Ergo if you generate 5000 you would be paid 700 ie nothing for the first 1500, 16% of the second 1500 and 23% of the remaining 2000. ( I hope my maths is correct! )
I have tried to manipulate other solutions using sumproduct but my knowledge is poor, the formula I have tried manipulating is =SUMPRODUCT( (A2 > {0,1500,3000,8000}) * (A2 - {0,1500,3000,8000}) * {0,0.16,0.23,0.3}). I prefer single line formula rather than lookups as staff will not be able to see commission rates easily.
View 4 Replies
View Related
Jun 12, 2014
I'm looking for a formula to do the following:
I'm building a table (see attached) where I could have a value in cells D6 to P6 that ranges from 0 upwards. In the respective cell beneath each 'value' cell (D7 to P7) I need a formula that will return a figure of 10 if the value in the cell above is 0.4 or below reducing to 0 if the value in the cell above is 1.0 or above.
View 1 Replies
View Related
Aug 16, 2014
Need formula to pic required data from Validation,
In the excel sheet Orange color (Data Validation) , If i select the data validation(Like, Jan-Orange- consumption) and corresponding data should display, so i required formula for that.
View 6 Replies
View Related
Dec 26, 2011
I have following table,
Item NoSectorDestination
12346589BOM-DXBN/A
12346589DXB-FRAFRA
12346589FRA-DXBN/A
12346589DXB-BOMN/A
87665976NAG-BOMN/A
87665976BOM-DXBN/A
87665976DXB-PARPAR
87665976PAR-DXBN/A
87665976DXB-BOMN/A
87665976BOM-NAGN/A
Row 2 to 5 are of same item no. The result that is required is in Column C. The formula to check same item numbers in column A and give the result as "FRA" in column C3 with C2, C4, C5 showing as not applicable. Similarly for the next item no
View 3 Replies
View Related
Apr 15, 2008
i have a list of names which also contain e mails addresses after the names, they are displayed as (In column B onmy spreedsheet)
Armani Stevens/GB/companyname/GB@soso
what i need from the above is just the name (up tp the first backslash)
so i would need Armani Stevens and disregard the rest.
What i would then need to do is to take the name and then see if the names is in the list which is situated in column A,
so to sum
once i have Armani Stevens extracted from Coulmn B i would want to see if this name is in Column A
Names are obviously of all different lengths and there is always a space after the first name and surname
View 9 Replies
View Related
Feb 12, 2009
A pal at work runs a football predictions spreadsheet but due to a recent surge in numbers, it is taking longer to adminster each week.
Basically, you get 3pts for a spot on home score, 4pts for a spot on draw, 5pts for a spot on away win and only 1pt if you get a correct result but not the actual scoreline, with no points being scored for being completely wrong.
At the moment, he manually inputs 0,1,3,4 or 5 pts each Monday, of course this is open to human error. Is there a way of getting excel to calculate this for him once he inputs the correct score in the first column?
View 9 Replies
View Related
Apr 18, 2006
The example Spreadsheet shows the current method of Ascending or Descending
Data according to Macro. Is there a way to replace the current Ascending or Descending macro code with a formula for the required cell groups only?
View 2 Replies
View Related
Apr 1, 2009
A1, B1, C1 and D4 are cell in Excel worksheet and values are 2, 4 and 6 in A1, b1 and c1 respectively.
A1 B1 C1 D4
2 4 6 C1-Formula is: =sum(A1+B1)
Now I have to write formula for D4-cell:
i.e. =sum(C1/Total days remaining of the month)
Suppose Today is April 01, 2009, C1/should be devided by 30,
Tomorrow will be April 02, 2009, C1/should be devided by 29.
View 10 Replies
View Related
Sep 24, 2012
i am trying to work with a formula that will look at date today (NOW) and compare this to a due date and in return provide me with only the working hours total. Working hours are 8am to 4pm (8 HOURS).If the due date is passed this will be a negative figure.
View 9 Replies
View Related
Aug 24, 2014
Data is;
83300 - hypothetical number of times I have fired my gun at target.
43209 - hypothetical number of times I have scored bullseye.
So, my bullseye percentage = 43209/83300, or ~51.87%.
Need formula to determine how many more consecutive bullseyes I need to shoot, in order to achieve 70% ratio.
Since each shot from now on will be a bullseye, both values (hits & shots) will increment together.
View 5 Replies
View Related
Aug 2, 2009
I'm creating an (English) football predictions competition for me and my family.
One problem that has stumped me is how to get the scores based on the 'home' & 'away' score predictions.
The rules are: If I predict the correct exact result I get 3 points. I want to add another 'rule' whereby if I predict the correct winner, I get 1 point. Incorrect predictions get 0 points. I don't know how to do this using a formula.
View 11 Replies
View Related
Mar 27, 2014
I have 1200 doctor/patient records to input into an excel spreadsheet for import to an online EHR database. I can set up all the normal formulas and formatting but for the life of me not figure out how to create a custom formula to take the first letter of the patient first name and last name and add 6 figures to create a unique patient identifier.
ie. James + Smith+ random 6 figures = JS245318.
In my spreadsheet the first name is under Column 1, Last name Column 3 and the unique number generated in column 4.
View 10 Replies
View Related
Feb 22, 2009
I'm working on a spreadsheet to compile and print checks. All the check information except the date and first check number is contained in a Wins sheet. I also have a Checks sheet which contains a master check. I used text boxes on the master check to contain individual check information. Each text box is filled in from the Wins sheet by means of formulas. The problem I'm having is how to modify the formula entries in the following code to cycle or loop through all checks required. A sample spreadsheet is attached.
View 2 Replies
View Related
Nov 12, 2009
I have table which looks like this:
Name of School Time1 Time2 LoggedIN
2436 DAVIS JH 11/10/08 8:20 AM 11/10/08 8:30 AM 1 2436 DAVIS JH 11/10/08 8:30 AM 11/10/08 8:40 AM 1 2436 SVM JH 12/11/08 7:00 AM 12/11/08 7:10 AM 1 2436 DAVE JH 12/11/08 7:10 AM 12/11/08 7:20 AM 2 2436 CRAW H 12/11/08 7:20 AM 12/11/08 7:30 AM 2
Total Number of Columns = 4
First Column = School NAme
Second Column = Time 1
Third Column =Time2
Fourth Colum = Logged In
I want a 5th column where i can get the following result:
If time in 3rd column is betweeb 8am and 9 am and time in the 4th column is between 8Am and 9 Amthe thee corresponding row in the 5th coulmn should be 8AM-9AM
If time in 3rd column is betweeb 9am and 10 am and time in the 4th column is between 9AM and 10 Am the thee corresponding row in the 5th coulmn should be 9AM-10AM
Likewise for each hourly time slot of the day
The time columns are in the "DATE" Format!
The Purpose is finally to get the number of students logged in each hour of the day,and finally draw a usage hour graph by school and time !
View 9 Replies
View Related
Aug 19, 2014
i am trying to remove the unknown character and extra space from the name. Though i use formula as trim or proper(trim), it is not removing the Unknown character / extra space. I have attached the few name as sample. Formula to remove these Unknown character / extra space, double space, special character from selected cell?
Note : I am using ms office 2013
View 3 Replies
View Related
Apr 19, 2014
In Cell A1 I have the date. In Cell B1 is the shift. There are three shifts labeled as 1, 2, and 3. Is it possible to create a rule that will convert 1 to 0700, 2 to 1500, and 3 to 2300? Below is an example of how I need the shifts converted to time (military).
[Code].....
View 4 Replies
View Related
Aug 1, 2007
I have a spreadsheet that users input the temperature each hour, for a 24 hour period. The time used is 24hr clock, as example, cell A1 will be 03:00, and cell B1 would be the forecasted temperature.
On another sheet, the user inputs the temperature for the specific time a airplane is going to take off, i:e: 07:32. Is there a way I can have the second sheet look back to the first sheet and grab the temperature for the correct time (using the hour block it falls into). The first sheet time blocks are every whole hour, i.e 08:00, but on the second sheet, the time could be any hour/minute, 07:32.
View 9 Replies
View Related
Jun 20, 2008
I am trying to subtract 8 hours from a date/time. What I need to do is to capture that lead time using only weekdays, and working hours of 8am-5pm.
For example:
Date/Time
6/23/2008 9:18AM (Monday)
Subtracting 8 hours from this scenario would give me
6/20/2008 10:18AM (friday)
View 3 Replies
View Related
Jun 15, 2014
basically I am trying to make a time calculator for work, I want it to automatically add the hours up but then also minus break times and then give me a whole paid total hours worked that week.
CALCULATOR.xlsx
View 1 Replies
View Related
May 1, 2009
I am looking for a formula to calculate time from one date and time to another. The only catch is I only want it to use times during specific times.
Example
Start 5/1 8am and end 5/2 8am. The working hours are from 7am until 4pm and from 8pm till 5am. In this example the solution should be 17 hours.
How do I set up a formula to respect only working hours (also excluding weekends except for Saturday early am from midnight until 5am.
Sorry if this is confusing, I am trying to use IF statements but I’m fighting a losing battle.
View 7 Replies
View Related
