Multiple Condition Involving Date
Feb 27, 2008
I have this problem on putting the right formula. Column A entry are dates corresponding to replacement of parts x at column B with running hours of parts x at column c respectively. the table will look more like this:
A B C D
1 jan 2, 2008 brake front 200 ?
2 jan 20 , 2008 clutch 150
3 jan 30, 2008 wheel front left 300
4 feb 2, 2008 brake front 50
5 feb 5, 2008 brake left 85
6 feb 15, 2008 clutch 300
the formula will first look at column B and look for the entry "brake front" and every time it matches the entry it then looks up the date and then displays the corresponding latest running hour value on column C at cell D1. D1 formula will be having a result of 50 as it is the latest running hour value compared to 200.
View 9 Replies
ADVERTISEMENT
Feb 8, 2012
I want to loop through a range of cells changing the value of each cell by increments of 0.01 until a value of 10 is reached.
If the loop encounters a blank cell I want it to start the loop at M110 again
If the starting value of cell M117 is greater than 10 then I want the loop to use -0.01 increments to reach 10
If the starting value of cell M117 is less than 10 then I want the loop to use +0.01 increments to reach 10
This is what I have written so far...
Sub Test()
Dim i As Single
i = 0.01
Range("M110").Select
Do Until Range("M117").Value = 10
[Code] .......
View 1 Replies
View Related
Jan 27, 2014
I am trying to put two subs into a macro. They work individually, but when I put them together, I get a "Compile Error: Variable Not Defined" Message. I was told I have to define the variable in the sub multipleif(), but I am not certain as to how to do this. The error message highlights the line I have bolded below.
VB:
Option Explicit
Private Sub CommandButton1_Click()
Dim mth As Variant, txt As Variant, des As Variant, wdt As Variant, I, Cell
[Code]....
View 5 Replies
View Related
Feb 2, 2014
I need cell (O4) to display days overdue or days remaining on an assigned task based on subtracting due date (M4) from todays date, but only perform days subtraction function if (M4) is not blank and only if task complete cell (N4) is less than 100.
Have the conditional blank cell figured out, but nesting another condition for the less than 100 complete cell. So in other words, I don't want cell (O4) to subtract days and display any error, irrelevant data if there is not any due date entered or the task is entered as complete...
View 3 Replies
View Related
Mar 25, 2014
formula that will compute contract amounts that are paying out for a given month, depending on what is listed as the contract renew date and how often the contract renews.
Assume the contract date is in C1, contract renew date is in C2, Contract term is a drop down menu in C3 that has two options of text, 6 Months or 12 Months. C4 gives the contract amount. There are then dollar amounts in C5 and C6 that are only taken into consideration if they are $0.00. In B1 will be the run date or the date I want to pull my info for.
I basically need a formula in C7 that will return the contract amount from C4 taking into account these conditions: If there is a dollar amount in either C5 or C6, return "$0.00" in C7. But if C5 and C6 are both zero, than: If C3 = "12 Months" and B1 is in the same month as C2 (I don't want it to take into account the year of day of the date), than return the amount listed in C4 in cell C7. Or if C3 = "6 Months", and B1 is in the same month as C2 or if B1 is 6 months ahead of the month listed in C2 (again not taking into account the day or year), than return the amount in C4 in cell C7. So basically if the contract amount in C4 was $100, and B1 3/1/2014 and the contract renew date is C2 is 9/15/2013, I still want $100 to appear in C7, even though there is only technically 5 and a half months in between the dates of 9/15/2013 and 3/1/2014. I want it to look at these dates and add six months to the month of September and then look at cell B1 and as long as that date is a date in March, return the contract amount in cell C7. I also want $100 to appear in C7 if the date in B1 was a September date. In the situation where C3 is set to 12 months, if C2 is a September date than $100 will only appear in C7 if B1 is also a September date.
And one last thing, I'm going to somewhat withdraw my request that the year shouldn't be taken into account. This is only partially true. It just occurred to me that the $100 should only appear provided the date in B1 is in the same year or in a subsequent year AFTER the year of the contract renew date in C2. So for instance if, C2 is 9/15/2013 and I put in 03/01/2013 in B1, it should NOT calculate the $100 in C7 since the contract technically would not have renewed in March of '13 as it didn't exist until Sept of '13.
View 1 Replies
View Related
Aug 13, 2009
I would like to try and achive a conditional format that will require a lookup function. when the condition is met it places Yes or No into a cell and changes that row colour to red for the NO condition. and after 10 days from now the rowcolour is removed.
View 6 Replies
View Related
Feb 20, 2009
I have several walking routes that are labeled A through H. Each walking route has a different distance. .18 miles, 1.45 miles, etc. What i would like to do is be able to enter what routes are walked in a day and have it display the milage in a total cell. Example......
View 4 Replies
View Related
Sep 7, 2009
I need to find a formula on cell A2 on the attached file. The formula should show me the count of Customers who buy SKU 1 only.
I tried to use filters across SKU (2-16) where there is "0" and then count all customers who have values >0 on Column B. But this takes a long time and I have to do similar tables quite a few times. I was just hoping if anyone can come up with a more efficient solution to it.
View 6 Replies
View Related
Aug 12, 2014
I have been currently working with Data in which involves time over 24 hours and I am trying to find the sum of these numbers.
An example would be the following :
4,889:38:33 +4,711:28:37 + 4,851:11:26 .
Of course, I have removed the comma and changed format, this did correct the issue. However, any way to find the sum with the comma still involved.
View 4 Replies
View Related
Mar 2, 2012
I have have a problem in my vba code
if my activecell is on example range("W78") then it will go to range("I5")
I made a code but it does not work, i cant use offset cause i have may columns but they all refer to the same column "I"
this is my example: Range("I & activecell.row").value
View 4 Replies
View Related
Feb 10, 2014
I'm looking for a formula that would look up the individual employee (i.e. Name1 for example) in the January sheet and then look for and sum any amounts that have a number and a symbol as reflected in A2 through A10 (8V, 4Vâ–²,etc), which would then be calculated with a similar formula for the February sheet, specifically the time period between 1/26/14 and 02/08/14 as indicated above.
As employees can switch shifts, the formula would have to locate now in February where employee (Name1) is and find that row for the same symbol/number calculations for this same time period in that given month. Currently I'm using an array formula which can be seen in the attached file in cell I4. The problem is that the formula assumes that Name1 is always in the same spot in all monthly sheets which isn't the case month to month. The formula looks like this without the vlookup portion:
[Code] .....
I also need to do a similar vlookup or match, etc with this formula too:
[Code] .....
Attached File : sample payroll.xlsm‎
View 7 Replies
View Related
Jul 22, 2013
In A1, I've got a time for an event expressed in military time without colons, like 1130.
In B1, I've got two more times expressed the same way, like 1230-1300. These are the time the event was actually began and ended.
I've separated these two times into four different columns, the first two (C1 and D1) containing the raw numbers 1230 and 1330, and the second two (E1 and F1) containing those numbers formatted as times (12:30 and 13:00) via the TEXT function =TEXT(C1,"00:00")
Getting the difference between these two times (expressed in minutes) is not difficult. What is proving to be difficult (however simple it seems) is checking to see if C1 (time event began) is within an hour of A1 (time event was scheduled.) I was able to get this to work with times not involving 0000 or having, say, 0130 as an event time and 2300 as the time the event was started, but those two cases (switching 0000 to 2400, and moving between "days") is causing a hangup. Checking if A1
View 1 Replies
View Related
Mar 3, 2009
Am doing some preliminary planning for a trip in Australia and am struck on a formula that will produce information like "Day 1" Day 2 and 3" etc.
I have done out this information manually and it appears in column E of attached copy of spreadsheet ...
View 9 Replies
View Related
Jan 29, 2014
I am using Excel 2003 and I need to count occurrences involving two colums like this: on sheet "SALES", column I has values ranging from 8.00 to 10.00; column M has positive and negative values.
formula to count how many numbers in column I exist between 8.00 and 8.99 associated to a positive value in column M? And likewise for negatives?
View 2 Replies
View Related
Nov 13, 2013
I'm using Excel 2003 and I need to calculate the number of occurrences on according to different criteria in two separate colums.
I am on Sheet 3 and the data I am analyzing is on Sheet 1, titled "RATES". I wish to know how many occurrences are when the word "GB" appears on column B (cells B2 to B100) along with a value greater than zero on column M (cells M2 to M100). The word "GB" does not appear alone but is part of a string of text with different words, I think this is relevant.
View 2 Replies
View Related
Apr 10, 2009
In range C15:C45 I have names of some steps. Starting from column D, row 6 have a drop down with 6 possibilities. Now based on what is selected in row 6 (so cell D6, E6, F6 and so on), I would like to color certain cells from 15 to 45 in that particular column (certain steps that are applicable to option selected in row 6 of that column). And as month goes by, users do that in more and more columns.
Also, steps overlap between conditions that are selected in row 6 and I would like this to macro (or formulae) to be running so that as soon as user selects the condition cells are colored and then they can fill out only those cells. So, I couldn't figure out any way to do this by conditional formating as there are more than three conditions and range is not continuous.see attached file as an example of final output.
View 4 Replies
View Related
Sep 9, 2012
I have two columns with two sets of dates which are nothing but durations of leaves.
Col 1 has start dates and
Col 2 has end dates
Start Date End Date
25 Jul 2012 5 Aug 2012
2 Aug 2012 8 Aug 2012
If I want to calculate difference between the two dates such that if a leave starts in July and Ends in Aug, then I want the result to be only the count of leave days in August. How can this be achieved?
View 7 Replies
View Related
Aug 8, 2007
i have in a
sheet1: those field below-->
STATUS DATE
ok8-Aug-07
ok8-Aug-07
ko8-Aug-07
ko8-Aug-07
ko8-Aug-07
ok8-Aug-07
ok8-Aug-07
i have to do some calculation in another sheet2:
if date = today count the number of ok else count the number of KO
View 9 Replies
View Related
Jan 12, 2009
I want to know the count of “s”, falling in a range of dates back (i.e.’7-12 mos. back, 4-6 mos. back, etc), preferably based on date modified.
Status Date Sold
A
S 11/1/2008
S 8/29/2008
A
S 7/12/2008
View 9 Replies
View Related
Feb 22, 2008
Column 1: Date Call is received by helpdesk
Column 2: Time Call is received by helpdesk
Column 3: Drop-down list indicating Priority of call (High,Medium,Low)
In Column 4 I want to enter a formula that adds working hours only to the date and time entered in Columns 1 & 2. The time added will vary depending on what is entered in Column 3 (e.g.: if priority is High add 1 hour, Medium add 2 hours, Low add 3 hours). The working hours i need to adhere to are 09:00-17:00, Monday-Friday.
View 6 Replies
View Related
Jun 19, 2008
I have a spreadsheet with about 225 sheets or so containing data. The date is in column J. I am needing to delete all the Rows that have a date greater than or equal to 06/06/08 and loop through all of the sheets.
I recorded a macro to find the date 6/6/2008, then highlight the row, then select the next 450 rows and delete them and it worked ok, but I don't know how to get it to loop through the sheets to the end.
View 2 Replies
View Related
Dec 22, 2013
formula using if condition. I attach the sample file about my problem.
View 14 Replies
View Related
Jan 25, 2007
I am trying to sum based on a set of conditions including time. The formula works fine except when time is between 11.30 pm to 12.00 am.I have attached the sample which will make the scenario clear. I am not sure how to modify the formula to ensure it works when the time is between 11.30 pm to 12.00 am.
View 8 Replies
View Related
Jun 4, 2008
I am trying to write a formula, that looks at row A1:A6 (Country), and equals A11 (Country chosen), but also looks at Columns C:E (Month), and equals B10 (Month Chosen). I can then add the figures in C3:E10. However I want a YTD idea, and so ADD Month 1 through to Month chosen in cell (B10) for the relevan contry chosen (A11). Uptil now I have used SUMPRODUCT, but am only able to sum one column, and not the required numbe rof columns.
View 6 Replies
View Related
Nov 13, 2011
with the formula below..
=SUM((Sheet2!A$4:$A$17475=AM2918)*(Sheet2!B$4:$B$17475=AN2918)
*(Sheet2!H$4:$H$17475="Left")*(Sheet2!$E$4:$E$17475>=AR2918)*(Sheet2!$E$4:$E$17475)
View 8 Replies
View Related
Dec 17, 2011
I've done this before but can't remember how I did it:
1BCDEFGH21234533red92701131096601005096604green20070582305250044940472805
blue0355203912033930389706bpink51059230632205352061280789-In column H,
I want to sum only rows that are less than or equal to cell H2.....
View 2 Replies
View Related
Jun 6, 2012
I want to find the most recent date in column D, when the variables in column E are all the same. There are many different variables in column E, and for each group of the same variables (eg. all cells containing "A"), I want to find which is most recently dated (dates are in column D). This is my code so far...
Sub adddiv()
Dim ticker As Variant
Dim freq As Long
Dim csheet As Worksheet
Set csheet = Worksheets("Sheet2")
[Code] ....
View 3 Replies
View Related
May 27, 2014
I got four product name in column & want to bring other product name equal to same same product in different colum
like if products name in column range is PRV IT should bring birkett in other colum
PRV SHOULD BE IN NEXT COLUMN AS BIRKETT
TANK " " "" WAM
INSTRUMENT " " " " AGI
HVC " " " BAILEY
and rest of should be Marston
I had put that formula but didn't work 100%
=IF(C5>="PRV",IF(C5>="TANK",IF(C5>="INSTRUMENT",IF(C5>="HVC","BAILEY","AGI"),"WAM"),"BIRKETT"),"MARSTON")
View 8 Replies
View Related
Jan 9, 2009
I've been trying to figure out how to do a vlookup using two conditions. Reading some other posts, I saw this could be done by combining a MATCH function but I'm still having trouble. Here is what I'm trying to do: I am trying to match cash amounts based on two look up values....one being the identifier (cusip) and one being the pmt code. http://i105.photobucket.com/albums/m204/RAK_08/data.jpg
This data is formatted exactly the same but comes from two different sources. I need to match the pmt amounts for a cash reconciliation.
View 4 Replies
View Related
Jul 18, 2014
I have added this example:
Test lookup main supplier.xlsb
I have a list of 5000+ items that I need to check (only 6 of them in this example in the red table).
I have a list of 25000+ items in a lookup table (only 10 of them in this example in the blue table).
Each item can have more than one supplier, but has only ONE Main supplier.
I need VBA code that determines if the supplier in B1 already is a main supplier for that specific item in the lookup list (yes or no).
I tried filtering it but that appears to be too slow for 5000 x 25000 items.
I have tried with VLOOKUP and Match, but I do not know how to combine the two conditions.
View 7 Replies
View Related
