How To Get The Starting And End Shift
Apr 4, 2014I have the problem to get the starting and the ending time in a timetable work sheet, adding the starting hour in the first cell and in the second the ending.
View 5 Replies
ADVERTISEMENT
Lookup Wage Calculations (calculate Pay Per Shift Dependant On The Type Of Shift)
Dec 1, 2009I have the basics set up, but need to work out how to make it calculate my pay per shift dependant on the type of shift i have worked.
I have attached a screen shot of the current page,
In it i have currently used validation drop boxes for the location and worked columns with tables just to one side of the sheet.
The shift pay is the column i am having trouble with.
I would like it to change dependant on what is selected in the 'worked' column.
For most things it should just display basic plus holiday, however if supervisor is selcted in the work column, it should display basic plus holiday plus supervisor.
Calculate Pay For Shift Work With Different Rates Based On Shift Hours
Apr 11, 2008a person works for certain hours and get paid according to the hours worked either by day or by night or a mix of both. Day payment is $8 when worked between 08:00 and 19:59 , night payment is $12 when worked between 20:00 and 07:59. The excel cell are formatted as datetime with yyyy-mm-dd hh:mm , the function works fine in getting the time information and checking whether the whole work is all day or all night , yet the if-then-else statements for calculation seems to be wrong!!
examples:
start = 2008-01-01 09:15 , end = 2008-01-01 11:40 , all day as it is between 08:00 and 20:00 and cost = 8/hr = 19.333
start = 2008-01-03 21:05 , end = 2008-01-04 02:05 , all night as it is between 20:00 and 08:00 and cost = 12/hr = 60.000
start = 2008-02-02 19:00 , end = 2008-02-02 20:05 , cost = 9.000 as 1 hour day = 8.000 plus 5minutes night = 1.000
Function prod(st As Date, en As Date) As Double
Dim shour As Integer
Dim smin As Integer
Dim ehour As Integer
Dim emin As Integer
Dim stod As String
Dim etod As String
pday = 8
pnight = 12
shour = Hour(st)
smin = Minute(st) + shour * 60
If (shour >= 8 & shour < 20) Then
stod = "day"
Else
stod = "night"
End If
ehour = Hour(en)
emin = Minute(en) + ehour * 60
If (ehour >= 8 & ehour < 20) Then.................
Start Shift And An End Shift
Nov 18, 2009I have a Start Shift and an End Shift time,
Start Shift = 2009/11/10 09:27:06 (GMT-6:0)
End Shift Time= 2009/11/10 15:13:03 (GMT-6:0)
eg. Total Time = 5.3 hrs
I would like to take if from this format, and calculate the total time difference. Sometimes the GMT codes may be -5:0 if that means anything. For the cell "Total Time" I only need it to have a decimal format.
Sum Starting From A Row?
Oct 26, 2011I have a column with number of Km from location x to location y (A), and a column with fuel supply (B). I what to know how many km make the driver after the fuel supplyes (787 Km - for exemple from bellow).
I was thinking to SUM values from column A starting ROW 3 (column B) (first cell non blank), but i don't know how to match SUM and ROW functions.
A B
1 150
2 200
3 250 50
4 120
5 260
6 157
Bar Graph Not Starting At Zero?
Jun 5, 2014I'm working on a stacked bar graph based on the below info...
Min
Mid
Max
N1
$21.00
$30.00
$38.00
[code]....
I'm trying to adjust the starting point so that it does not start at 0.
For example...
N1 - $21.00 - $38.00
N2 - $26.00 - $45.00
N3 - $30.00 - $53.00
is there a way to do this?
Starting With UserForms
Jan 24, 20081) I have added multiple command buttons as options within different categories but when I select one, all others are reset. Is ther a way to section off the command buttons into groups so that say the first three act together, the next two act together and then the last four act together, allowing a total of three options within three different categories?
2) How do I get the options from the userform to the worksheet?
3) How do I put an input box on a user form?
Sums Starting Over After Zero
Feb 25, 2009I need a formula that will help me sum a row of numbers but, if at anytime there is a zero it should give me zero and the sums should start over at 1.
View 9 Replies View RelatedHot Key Not Starting Macro
Oct 4, 2006I have Ctrl+x to run a macro and it is not working. I have used it before fine, but made modifications this time and am having problems now. I'll just show the code for now and can give more details if the fix is not obvious.
Sub PO_Flash_Report_Detail() ....
Selecting A Starting Line
Feb 10, 2010I have a code that print previews a certain amount of rows that I define. This basically says print preview the first 10 lines down.
Can the code be modified so that i can select a start cell. So instead of always starting at A1 I could select say start at G1?
Sum Subsequent Numbers Starting From 1
Aug 31, 2012Is there a built-in function in Excel to sum subsequent numbers, starting from 1?
If I give for example 5 as input value to the function, it should return 1+2+3+4+5 = 15.
If I give 3 -> it should return 1+2+3 = 6 and so on.
Macro Starting On Certain Criteria
Nov 16, 2006im trying to learn abit of vba and have put some macros together however cannot figure out where to start for the following
when the workbook opens it checks sheet2 : c10 ="Y" then sheet3! is displayed. If sheet2 : c10 = "N" then opens normally.
Macro's Starting From Different Cells
Nov 19, 2007I want to build a Macro that basically moves right of the cell I am in a few cells and then copies and pastes this info into another worksheet and then prints this worksheet.
However, I can't seem to build a macro that will run from whatever cell I am in.
Everytime and try it just goes from the cell I was in when I recorded the macro.
For instance, if i build the Macro in row 1, but then want to run the marco in row 23, it keeps going back to row 1 whenever I press play.
I know there must be a way of telling it "move right 5 cells from whatever cell is highlighted when the Macro is run...etc"
Displaying Numbers Starting With Zero
Apr 28, 2008I'm trying to display payroll numbers (eg 0001234), but the zeros are not displayed. How do I get this to happen?
Remove Customers Starting With A
Mar 30, 2009I need some code to add to a macro that will delete customer account names that start with an "A" in column C. Specifically it needs to find customer names starting with the letter "A" and delete that entire row.
Starting A If And Elseif String
Apr 13, 2007In row 6 column O I need for the formula to look at column I and determine if the month is 1, If yes then I need for it to determine if the date is one of the following,
Day(I6)>=1,Day(I6)<=5), If TRUE Then do the following
Cells(J6)*1.04)*2080/12
If False then
ElseIfDay(I6)<=6,Day(I6)=<12, If TRUE Then do the following
Cells(J6)*43.33)+((J6*1.04)*129.99
If False then
ElseIfDay(I6)<=13,Day(I6)=<19, If TRUE Then do the following
Cells(J6)*86.66)+((J6*1.04)*86.66
If False then
ElseIfDay(I6)<=20,Day(I6)=<26, If TRUE Then do the following
Cells(J6)*129.99)+((J6*1.04)*43.33
If False then
ElseIfDay(I6)<=27,Day(I6)=<31, If TRUE Then do the following
Cells(J6)*1.04)*2080/12, but put this into column P
If row 6 Column O if the month is greater then 1 then I need the formula to do the following,
=J6*2080/12
What I am trying to do is have excel take columns O through Z look back to column I and determine which month the performance review is due in. Then I need for it to determine which week the increase rate will be effective for. So for each month the “If the month is >1” would change to 2,3,4,5,…..and so on.
In addition I am going to have the formula look at the shift and if it =3 then (J68)+1.00, but for the increase I have to have it -1.00 then J6*1.04+1.00. I will also have to create a table that holds the max rate for each grade, and then figure out the formula to figure out this new rate.
Return Information On Same Row If Starting Value Is Correct
Feb 11, 2009I need the formula for statistical data analysis for a research project i'm helping out with, and this is too complicated for me to know where to even begin searching for the information. The problem is this:
I have a sheet (called Sheet1) containing 9009 rows of information on columns A to AL. I am interested in searching for the starting sequences of codes contained in column AI and returning values on the same row but in a different column. The formula that works so far is this (note: I use Excel 2003 and a swedish version, translated the formulas though)
=IF(ISNA(MATCH("N06*";Sheet1!AI:AI;0));"error";INDIRECT(ADDRESS(MATCH("N06*";Sheet1!AI:AI;0)*1;1;1;T RUE;"Sheet1")))
I havent come up with this formula entirely on my own so dont think my expertise is this high In any case, this formula gives me the value of the A-column in the same row as the first N06 found in the AI column. If N06 isn't found anywhere in AI, it gives me "error". The problem is this: I want it to "continue" searching column AI for strings starting with N06 and returning the values in the A-column for the following N06s found. I basically want to wash out all rows of information that dont contain the string N06 in AI, and get the new information on another sheet.
To make it even clearer: I want to have all values on column A on a new sheet, but only where the AI value on the same row starts with N06. Eventually I want other columns as well, and also search for other codes, but that should be easy to figure out once I get the initial formula.
INDIRECT And Sheet Name Starting With C01V
Mar 2, 2009I am using the INDIRECT function to get data from sheets with different names, with the format INDIRECT(SheetName&"!$A$1"), with SheetName defined as the contents of a particular cell which contains the name of the sheet. This works fine with the exception of sheets which have names starting with C01V. Is there something special about these names? If I change the 0 to O or the C to A, B or D, there is no problem. Sheet names C0, C01 also don't have any issues.
View 7 Replies View RelatedSum A Range Based On A Starting Lookup Value
Mar 2, 2009I'm trying to sum a range of data consisting of 12 cells (each range is in a unique row going across adjacent columns). The range of data to be summed is based on a lookup value which is a column header - I want the range to start with this lookup value. In column B of my spreadsheet, I have a 'First Activity number code' going down...In Row 4, I have the same First Activity Codes going across the row (the column headers). In Column U, going down the column I am looking for my formula.
So For example, in row 5 of my spreadsheet, I am looking at 73 as my base code (the value in column B), and I want a range of data summed that lies in the same row where the column headers are 73 - 84 (12 rows) - always starting summing the range with the same number that is in column B. So in row 18, 78 is my base number; so I would want the sum of the numbers in the same row where the column headers are 78-89 ( 12 columns).
Acessing Starting Folder Path
Oct 2, 2008I start an Excel program from a shortcut in different folders. I know that I can find the path of the Excel program in "Parent.Path". But I don't know how to find the path of the folder the shortcut was in. It should be findable because when I try to do something like <Save>, Excel knows the correct folder there.
An example: I open Excel in "Folder 1" using a shortcut to the Excel Program in "Folder 2". Parent.Path tells me "Folder 2". How do I find out what "Folder 1" is from inside Excel?
Populate Form Always Starting From Column A
Jan 20, 2009I have a form pop up when user runs a macro and it populates the form beginning with ActiveCell. How do I always populate the form beginning from column A (row based on ActiveCell)? It would be really helpful if I could just set cl to take the ActiveCell and back it up to column A.
View 7 Replies View RelatedVLookup Starting At A Cell Header
Nov 1, 2011I'm trying to do a vlookup but I want to be able to change the table array starting point depending upon a defined cell header. so
=vlookup(a1,sheet1!b1:c5,2,false) where b: would be defined by a header as
=vlookup(a1,sheet1!header name:header name+1,2,false).
So it will work much in the same way as match does for VLOOKUP
($A2,'Sheet 1'!$A:$FG,MATCH(AP$1,'sheet 1'!$1:$1,0),FALSE) but the array needs to change.
By doing this my vlookup will work automatically depending upon the header.
Create Sequence With Formula Starting In A1?
Apr 19, 2013How can I create a sequence with a formula starting in A1 that will give 1,1,2,2,3,3 and so on horizontally?
View 3 Replies View RelatedDelete Row Based On Starting Character 1st Then Contains
Jun 23, 2007I would like to create a macro that deletes all rows starting with an open parenthensis in column B. The contents of what is in parenthis varies from 2 letters up - so anything starting with a parenthis is sufficient criteria.
After the initial deleting (mentioned above), would also like to delete (from column B again), rows containing specific, multiple phrases.
None of the functions I have see so far will facilitate this...
Find Words Starting In - Macro
Nov 10, 2007I would love to be able to click on an assigned button in a new sheet, and a pop box appears.
Then I can type in a word, click find, and the macro will go through my whole list of keyword phrases and find all the words STARTING with the word chosen in the pop up box.
Yes, I could just sort from a-z, find the word, then copy and paste etc.
But I work with large lists sometimes, and its just so much easier to type the word, click find and the macro returns the list of just the phrases with the starting word I've chosen
As a note. I did put a post up a while ago which a coder called Jindon solved which works perfectly. (A great job by the grand master)
This macro finds the words ending with.
So, what I would really like is exactly the same macro, but it finds words at the start of a phrases not the end of a phrase.
I'll post Jindon's macro now
Sub Find_Words_Ending_In()
Dim sTime As Single, res As String, msg As String, x As Range
Range("c3:c" & Rows.Count).ClearContents
res = InputBox("Enter word to find")
If res = "" Then Exit Sub
sTime = Timer
On Error Resume Next................
Auto Number Macro Starting At 5th Row
Jan 25, 2006I need to number some rows starting at row 5. Need to place the number in column AB of each row.
Needs to quit numbering at last record (column F will have information in the last record)
i need this to be a macro, never done an autonumber macro before....
Starting Path For Open Dialog
Apr 21, 2006Is it possible to set the starting path for the open dialog - using expression. GetOpenFilename so that the contents of the correct folder are visible immediately?
View 3 Replies View RelatedDeleteing Rows Starting With Value (text)
May 22, 2006Using an external UNIX program that quieries a database, I export all the data found based on my query and export the report as a text file. THe text file can be up to 1 MB of raw text.
Selecting all text and pasting the data into collum 1 using PASTE SPECIAL -->(TEXT)
All Data is in Collum A
Each record returned has between 40-50 values each on a seperate row, only 10 of which I need.
I am looking for a way to find the rows that begin with the unwanted field, and then delete the entire row.
Otherwise ignore the row.
IE:
AUDFI : (data path loc here)
BILLNUM : 060606
CUTNUM : (0000)
SD : 120012
ED : 121259
CC : 123456789 MATERIAL LISTS AND ITEMS
COMMENT : Multiple lines of text here
with continuing free form data, numbers, operators,
then the next data file would repeat:
ADUFI : (data path loc here)
...
...
...
etc.,
In the above example, I want to delete the rows AUDFI, & CUTNUM and move the below items up to take the empty rows place. The BILL NUM, SD,ED, and COMMENT are filed that are always kept (as well as others). Each record has roughly the same data, each tailored to an event or object.
The number of rows of data dumped into excel can easily exceed 50000, about 200 - 1500 records in the report.
After wards, the data is dumped to a text file for easier analysis.
In BASIC my line might look like
5 START
10 if A:$="AUDFI :" then delete row, move below contents up.
20 if A:$="CUTNUM :" then delete row, move below contents up.
30 END
(I think there needs to be a loop since the vaules AUDFI and CUTNUM (as well as other unwanted fields can occurr 200 - 1500 times each.)
Import From Txt File Starting At A Specified Line
Mar 28, 2007I am using Chip Pearson's example of importing text into my worksheet.
Sub ImportTestFiles()
ImportTextFile "C:Documents and SettingsKevMy Documents est.txt", ","
End Sub
Public Sub ImportTextFile(FName As String, Sep As String)
Dim RowNdx As Long
Dim ColNdx As Integer
Dim TempVal As Variant
Dim WholeLine As String
Dim Pos As Integer
Dim NextPos As Integer
Dim SaveColNdx As Integer
Application. ScreenUpdating = False
On Error Goto EndMacro:
SaveColNdx = 1
RowNdx = Range("A65536").End(xlUp).Row + 1
Open FName For Input Access Read As #1 ...
Dynamic Range When Starting Cell Not Known
Jul 4, 2007I found dynamic range name across columns:
=OFFSET($A$1,0,0, COUNTA($A:$A),COUNTA($1:$1))
which expands across Columns in Row 1.
What if we don’t know range to be named in which row or column starts?
What will be a formula (assume range starts on cell named “BegRng”)?
