Multi-Conditionals Based On Overtime / Weekends And Hourly Minimums?
Apr 8, 2013
I'm having trouble with conditionals based on a primarily a 4 hour minimum. I am trying to set it up so that anything under 4 is shown as the minimum 4 hours + Travel Time - Lunch Time or if it is greater than 4, shown as Regular Hours + Travel Time (since travel cannot be overtime) - Lunch Time & Overtime (anything over 8 hours). Additionally, I was wondering if it would be possible to incorporate an additional IF formula based on the date. If the entered date is on a weekend, then all hours worked will need to be overtime rather than regular.
View 1 Replies
ADVERTISEMENT
May 21, 2014
calculate overtime in California. I found a few previous posts, but none that meet all of the requirements. Employees get overtime in CA for:
More than 8 hours are worked in a day (up to 12)
More than 40 hours are worked in a week
Hours worked on the 7th consecutive day (up to 8)
Employees get double overtime for:
More than 12 hours are worked in a day
More than 8 hours worked on the 7th consecutive day
To eliminate the the 7th day issues, I am just using helper cells for hours worked on the 7th day of the work week. So far, what I have is what I found in a previous post:
=MAX(0,SUM(A13:A26)-40-SUMIF(A13:A26,">8")+8*COUNTIF(Daily Total Hours,">8"))+SUMIF(A13:A26,">8")-8*COUNTIF(A13:A26,">8")
This will calculate the hours of overtime over 40 in a week and 8 in a day, but will not differentiate between hours 8-12 and hours 12-?
View 1 Replies
View Related
Jan 17, 2009
I have a code that copies numbers form a worksheet (let's say "Worksheet1") and pastes them into column "F" another worksheet (let's say "worksheet2"). My code does that and sorts the numbers...That's not the problem... The problem is that in the next column (Column "G") of the copied & sorted numbers i should put the numbers from 1 to 8, and if I have more than 8 cells filled, the program should put the numbers from 8 to 1 and so on... For example:
F G
480 1
321 2
250 3
220 4
210 5
200 6
180 7
150 8
120 8
100 7..........
View 9 Replies
View Related
Mar 6, 2008
I have the following formula in Excel:
=SUM(IF('PAYROLL TRACKING'!$B$10:$B$452='AE LIST'!$A2,IF('PAYROLL TRACKING'!$E$10:$E$452="No Data",1,0),0))
I am trying to get a count of each employee's clients where data has not been inputted. I attempted a COUNTIF() using an AND() but was unsuccessful. That led me to a previous tip on here where you can SUM() using a formula similar to above.
My result now is #VALUE!
View 9 Replies
View Related
Apr 5, 2007
Is there a way to calculate a conditional minimum? I have a range of 1000+ values and I need to find the minimum, but the minimum has to be greater than a specified minimum threshold.
View 2 Replies
View Related
Oct 31, 2007
2 columns from a table are:
Bill# Rates
1715 500
1715 600
1715 625
1716 750
1717 760
1717 780
1718 400
1719 650
1719 800
So there is a bill number with multiple rates. I want to find out the minimum rate for each bill number (for 1715 it is 500 and so on). Whats the easiest way to give a formula for each bill so it does it automatically? I know of SUMIF and COUNTIF but how to "MINIF"?!
View 9 Replies
View Related
Nov 19, 2012
I am trying to calculate over time based on shift time.
For example: Regular shifts are between 7:00 AM to 3:00 PM (Monday thru Friday). Anything between those hours and on those days should be considered REGULAR TIME. Anything between 12:00 AM to 6:59 AM or between 3:01 PM to 11:59 PM should be calculated as OVERTIME.
Anything on Saturday or Sunday should be calculated as OVERTIME as well.
View 14 Replies
View Related
Jun 30, 2014
I have a listbox with 8 columns. Multiselect is enabled, and it must stay this way. As part of my program, after the user presses a command button, I need to use the row indexes of the selected rows in order to copy the selected information into an array which is then placed in a different listbox, and then delete the items from the original list. Pseudocode of what I want to do:
[Code] .....
But my understanding is that .ListIndex does not work this way with multiselect listboxes. I've tried searching for a solution for a while, but I cannot find one.
View 5 Replies
View Related
Apr 28, 2009
I have attached a sample spreadsheet where I now want to move date from multiple rows to one row and multiple columns where data in A is common, hopefully spreadsheet shows this more clearly. I have shown sample on two sheets, before and after. I want to have all data in a single row for instances where A the same.
View 2 Replies
View Related
Dec 18, 2008
I'm looking for a formula (VBA I'm assuming) that will help me create a unique customer ID out of data that my website generates in order to import records into my accounting system.
I have a current list of customers in CSV format with the columns: CustomerID, CustomerName, CustomerZip
Each customer in our accounting system is assigned a unique,7 digit CustomerID in the format of XXX#### where XXX are the first 3 letters of their last name, and #### is a 4 digit number (with leading 0's) to create unique ID's for customer with the same first 3 characters of their last name. SAMPLE LIST:
SCH0001, Lindsey Schubert, 75230
SCH0002, Thomas Schoembs, 53132
ADA0001, Samantha Adams, 28205
...
What I'd like to do is pass the formula 3 parameters (Cust_First_Name, Cust_Last_Name, Zip) and have it parse the .CSV file and either return an existing customer's current ID or generate the appropriate new, unique ID, making sure in increase the 4 digit # accordingly and insert leading 0's if necessary.
Another caveat, if possible to work with, is the ability to also pass the formula another range of cells to append to the end of the .CSV file's data for comparison reasons. There are times when I'll bulk-import orders (or we receive numerous in the same batch) and the potential exists to have two customers that would have the same CustomerID created using JUST the .CSV data. Ie. If we use the example above and have new customers of Steve Schwab and Julie Schwitzer - we'd end up incorrectly assigning them both SCH0003, where if we'd read Steve Schwab's newly created info and customer ID of SCH0003, then Schwitzer would correctly be assigned SCH0004.
View 3 Replies
View Related
Dec 30, 2008
I have a large worksheet that lists employees and the hours they work each day in a pay period.
For Example:
Column A holds a date and Column E holds the number of hours worked for a paytype. So i want to create some function that sums the numbers in column E, but only those where column A holds a date.
Then i want to return that number to a cell, say C20. I think I've managed to tell the macro which rows i want to return values from, but I can't figure out how to say "add these numbers together and put the total in C20."
I'll check this frequently if clarification is needed.
View 9 Replies
View Related
May 12, 2006
Trying to count the number of fields that meet a criteria base in 3 different columns. Worked on this for few hours but still keep hitting the wall of no right answer. I have attached an example sheet.
Trying to find the Number of fields that are not blank in the range of B9:B28 and have no date (are blank) in range(C9:C28) and have a number that is < 7 in range (D9:D28).
View 3 Replies
View Related
Jul 16, 2013
Let's say I have one column of;
1
2
3
4
5
6
7
8
9
What is the most efficient way to change this into '3-columns & multi-rows' like this?:
1 2 3
4 5 6
7 8 9
The actual list is a lot longer and numbers are not in order.
View 7 Replies
View Related
Aug 21, 2013
I have multiple rows within a cell separated by Alt+Enter, and would like to combine them as follows:
Desired Result
First
First
Name
Name
First Name
First Name
View 2 Replies
View Related
Mar 20, 2012
I have attached my Excel File with the userform I am trying to do. What I am trying to do is change the table in the listbox based on the selection of the combobox and then my selection in the listbox will pass the selected values to Range A1:C1.
Excel 1.xls
View 9 Replies
View Related
Jan 8, 2010
I'm trying to calculate a multi-variable equation based off of data in a table with different criteria. If you reference the attached spreadsheet I am trying to calculate cost based on weight and zone. For the data entered in cells B1 and B2 944.01 would be the cost. If you look at the table in G2:N3 you'll notice that the values in G2 and G3 are the weight limits G2 is from 0-4999 and G3 is 5000+, this should impact where the value in B1 falls. B2 should select from H1 to N1.
View 3 Replies
View Related
Mar 14, 2008
I'm trying to populate a dropdown Combo Box by using conditions.
In the example I attached...I would be trying to use ComboBox1.ListFillRange to populate the ComboBox only with players who have "C" in the Position column and "ANA" in the Team Code Column. Which would give me all the Catchers on the Angels.
I've been trying to use worksheetfuntion.Index.
View 4 Replies
View Related
Aug 22, 2007
I have a dataset in one column. Here 4 cells should be turned into 1 in a new column using a middelvalue.
How do i get excel to do this, so it uses 4 new cells from the first row to every new cell in the new column?
View 12 Replies
View Related
Feb 10, 2009
I have been using the attached formula to calculate total volume (customers seen) by hour (between 1 and 2, 2 and 3 and so on...). But how can I change this formula to take a census at a certain period of time (let's do it on the hour to be easy) instead of double-counting those who were there, but left in the same hour and those who just entered in the same hour. I hope this makes sense...a true census really should not count two people twice though which I think is happening here...
I hate to create a sense of urgency, but would love to begin working on this later tonight. Please let me know if you need more detail.
View 8 Replies
View Related
Jul 14, 2009
I have an excel work sheet with 2 columns and hundreds of rows. The first column is time in minutes over a number a years and the second is a list of corresponding data readings (numbers). I want to only select on the hour data from the list however I cannot seem to do it. The times vary in the sense that they are not all just 1 minute apart, some are every minute, then there might be a 10 minute gap in data, a few more minutes, then another gap etc.. All I want to be able to do is to select all the on the hour data from the list and its corresponding data value.
View 6 Replies
View Related
Mar 27, 2007
I am trying to create a conditional formula. I have multiple workers that work for different $$ per hour. I am keeping the hourly rates on one page for security purposes (the person that is going to be updating the hours worked is not very computer savy and they less they have to type the better) and the hours on another... I want copy a formula down the page that will calculate the hours to the hourly rate. The issue is that i want the formula to look at all of the hourly rates (assume they get raises) for Joe and multiply the most recent hourly rate times the hours... in addition I don't want the formula to re-calculate when a new hourly rate is put into the hours sheet.
Example
Jan. 1 joe works 8hours at $10 per hour formula in A1 (or wherever) calculates $80
Jan. 2 joe gets $1 raise
Jan. 2 Joe works 8 hours at $11 per hour formual in A2 calculates $88 but formula in A1 maintains the $80 and does not calculate the $1 raise.
View 12 Replies
View Related
Nov 4, 2008
I have a list of days and hours, and data for whether a type of event either did or did not occur for each sampled hour. I need to return a new value, some sort of "Event Day" value, which will essentially tell me if an event occurred at least once for each day.
Attached is a sheet with 4 days' worth of data. Jan 1st and 3rd 2006 are my two "Event Days".
To complicate things, I can't be sure I have 24 hour readings for each day, so I can't assume each day is a data range (e.g. A1:A24). Hours might be missing. (As such, I am trying to organise along the lines of IF the preceding cell is the same date...)
I also have lots of data stored as months, so would love a formula I could just autofill down. All it needs to do is effectively flag up for me when a day has experienced one event.
View 4 Replies
View Related
Jun 2, 2007
I am trying to build a costings sheet for shift work. The first problem I am experiencing is getting excel to split up a shift into the different bands.
For example the shift needs to be calculated on different rate bands, from 00:00 to 08:00, 08:00 to 19:00 and then 19:00 to 00:00. There is a different hourly rate for each band. Therefore if I enter a start time and end time, I need excel to calculate the total charge over the different rate bands.
For example a shift 07:00 - 21:00 would have:
1 hour 07:00 - 08:00 at Night rate
11 hours 08:00 to 19:00 at Day Rate
2 hours 19:00 to 21:00 at Night Rate
Once I have this worked out I have the added complication of different rates on Friday evening,s, Saturdays and Sundays etc. But at the moment one problem at a time !
View 9 Replies
View Related
Mar 22, 2007
I have a list of values at 'x' min intervals (the intervals sometime changes). I want to sum each hour's values eg. between 0:0:0 hour and 0:59:59, I want to add values 1040+800+860+870. Then I want to proceed and add all values for the next hour i.e. between 1:0:0 and 1:59:59, and so on. I have about 65000 of these calculations to do, so the need for some vba programming.
Hour value
0:14:531040
0:29:53800
0:44:53860
0:59:53870
1:14:53850
1:29:53860
1:44:53710
1:59:53670
2:14:53610............
View 5 Replies
View Related
Sep 26, 2013
i have a s/sheet with thousands of times in a column (eg col1 below in 24 hour format) and would like these in an adjacent column in hourly intervals (eg in Col2 below
e.g.
Col1 ColB
00:23 0 - 1
02:54 2 - 3
09:07 9 - 10
15:24 15 - 16
Is there a formula I can run to enable?
View 2 Replies
View Related
Nov 6, 2009
I have a large sheet with the values of power from a counter in a 15 minute base.
A B
01.01.09 00:15 0,25
01.01.09 00:30 1,15
01.01.09 00:45 0,75
01.01.09 01:00 2,01
and son on until the end of the month
What I am trying to do is to create a macro which calculates the average per hour and put in another columns like this:
01.01.09 01:00 average from 00:15 until 01:00
01.01.09 02:00 average from 01:15 until 02:00
and so on until the end of the month.
View 9 Replies
View Related
Dec 22, 2009
1/1/20080:221/2/200814:051/1/20082:451/3/200819:301/1/20089:001/1/200819:33
In time Out time
I 've used the site many times in the past, but this is my first post.
Above is a small example of the data Im working with. I'm not wanting to plot all of this out by hand, so hopefully someone can help (there are around 30,000 samples). I'm looking to track by hour when a customer is actual here. The first row is one customer in @ 00:22 and out the next day at 14:05.
I would like to have dates of the year down column A and hours 0 through 23 across row 1. Then a number of customer here on 1/1/2008 by every hour of the day on to 1/2/2008 so on and so.
View 9 Replies
View Related
Apr 13, 2006
I have a macro that I want to run on an hourly basis, say at 9am, 10am, etc whenever the workbook is open. This needs to be definable as I have several users that will be conducting the same activity throughout the day. In other words I want the macro to run on the hour for user 1, quarter past the hour for user 2, half past for user 3 and quarter to for user 4. Each user does have their own unique macro to run so it should (hopefully) just mean introducing a line of code to each, defining the time to run.
View 4 Replies
View Related
Mar 14, 2013
I have a string of data containing hourly timestamps with a value attached to each timestamp.
I need the timestamps to be every 15th minutes instead of hourly. So basically, I need the hourly value to be continued four times for each timestamp.
See the attachecd workbook. It should be quite simple, but for some reason I just cant figure it out!? A bit embarressed In the woorkbook, I need VGTs and VPTs to be in a 15 minutes granularity.
ERROR: Cannot upload workbook - uploader not showing so here are shot of the raw data:
VGTs
VPTs
01/02/2013 00:00
[Code].....
View 9 Replies
View Related
Mar 26, 2013
I have large data sets with half hour values that I need to average into hourly values. Here is an example of the data:
28.12.2012
01:30:00
0,1
2,4
2,5
127,6
28.12.2012
02:00:00
0
0,9
0,9
118,5
[code]....
View 5 Replies
View Related
