I am trying to make a condtional formatting to a cell, so that it will change colour depending on the time since the date in the cell. I am trying to check for Less than 6months, equal to 6months and greater than 6months.
View 4 Replies
I have start date(Column A) and an end date(Column B) in two columns and I have found out the difference between them in column C in hh:mm:ss format. I want to find out how many cases are greater than 2 Hrs but less than or equal to 4 Hrs.
View 9 Replies View RelatedIn my Excel spreadsheet I enter todays date in a single cell (A2), then I list various dates that jobs come into shop in other cells (A8:A108). I have cells ( F8:F108) where I have been manually entering an asterik (*) for those jobs equal to or greater than five days old in cell (A2). Is there a formula that can do the math for me? I've tried Excel help but to no avail.
View 3 Replies View RelatedI'd like all Cells in column AC (e.g. AC$3$:AC$517$) to be filled with yellow fill if the cell value is any date greater than or equal to today. Any past dates can be left blank (for now)
=$AC3>=TODAY()
I've got the following function that check if each cell has a date greater than TODAY(). If result is true, it'll display "NO GO". Otherwise, it'll display "GO".
I would want to improve on it such that if any of the 'B5:F5' cell is empty, it'll display "Incomplete" instead of "No Go".
I have a cell, M87. The score in M87 can be less than 13 or greater than 25. I need a formula within M94 which refers to M87, and outputs depending on the the following criteria. If M87 is less than 13 then output as D. If M87 is 14, 15, 16, or 17 then output as C. If M87 is 18, 19, 20, 21, 22, 23 or 24 then output as B. If M87 is greater than 24 then output as A.
View 3 Replies View RelatedIf A7 is equal to and greater than 95% than B7 is equal to "A". If A7 is equal to 91% but less than 94.99% than B7 is equal to "B"...
View 4 Replies View RelatedI'm trying to sum all values greater than 5,000,00 and less than or equal to 10,000,000. I have this formula:
Code:
=SUMIFS(B6:B1359,B6:B1359,">"&L2,B6:B1359,"=
I am looking for a function like MATCH if the match type were set to -1. However my data is sorted in ascending order. I am mining data from a Pivot Table, and it has dates across the top. Of course the pivot table will have the data sorted in ascending order from left to right. I want to find the first date that is greater than today. With weekends and holidays I can't just use TODAY()+1. Is there a function that can do what I am asking? Also I do not want to change the pivot table itself.
View 9 Replies View RelatedI need a formula that looks at the total in H40 and if the number is between 32 and 40 I need it to return the number then if the number exceds forty I need to multiply the overage by 1.5 and add it to the 8 for a total of 11.
I think it would be something like:
SUMIf(H40>"32",
I am creating my first Userform and having some problems. I take the data supplied by the userform and try to match it as closely as possible to a row of information. Currently I am using four cells to autofilter my spreadsheet data. Two of the cells I am looking for a exact match. The other two cells I am looking for the number that has been input or anything greater than it. Here is the code I have come up with...
View 9 Replies View RelatedHow can I count the number of cells in a range that contain values that are greater than or equal to zero?
View 2 Replies View Relatedi'm trying to create a markscheme progress sheet.
You take a childs actual age in months and years (ie 9.8 for 9years and 8months). They then, in theory, should be at that age level for reading and spelling.
I have the age in B4, the reading in C4 and the spelling in D4. If i use CF to say i want C4 to be red if it's less than B4, Amber if it's the same and green if it's over, as soon as i okay it, C4 goes amber, because it's equal to B4, which is blank. I can get the colours to work, i just can't figure out what to put to turn the amber off when cell B4 is empty.
I've tried as many combinations as my little amount of excel knowledge can cope with.
i have table of values if any value in the coulomb G and I greater than or equal to 1 the entire row must be deleted how can i do this ?
View 4 Replies View RelatedStarting in cell F3, if Column F is greater than or equal to zero, delete the entire row and continue deleting rows until Column A has contents in it. Then go to Column F in that same row that had contents in Column A, if the contents in that row of Column F are greater than or equal to zero, delete the entire row and continue deleting rows until Column A has contents in it. Persist with this pattern until every row in Column F has been checked.
View 4 Replies View RelatedNeed a formula for counting how many consecutive cells where the value is gretaer than or equal to +1
For example 1,
J18 = 7
K18 = 9
L18 = 3
M18 = 2
N18 = 8
O18 = 1
P18 = 8
Q18 = -17
In the above example the result would be 7
Example 2:
J12 = -22
K12 = 9
L12 = 4
In the above example the result would be 0 since the 1st value was -22
I am revising a spreadsheet to automate some cells and size cable for my job. I am currently using the VLOOKUP command to search a table for the proper ampacity and return a cable size based on the minimum circuit amps. The problem however is the VLOOKUP command searches for a number equal to or less than my circuit amps and returns this cable size. I need it to return a size equal to or greater than the minimum circuit amps. How do I get it to lookup something equal to or greater than the minimum amount?
View 9 Replies View RelatedI am using cell V7 to input the formula but I want cell V7 to tell me if the number in U7 is greater than or equal to the individual #'s in cells U7-U40 and I would like it to put a 1,2,3 in the cells of V7-V40 for the three highest numbers in order of largest to smallest if is this possible?
View 3 Replies View RelatedI want to display a warning pop up message when the following cells (Cell A1:A10) is greater and equal to 3.
I would like some code in VBA
I have what may be a math question as much as an Excel question. See my attached spreadsheet example in which I grow the denominator of a ratio by a fixed percentage until it equals or exceeds the numerator. This results in the denominator being grown X number of times.
View 2 Replies View RelatedSUMIFS with less than & more than
I found this thread which works for normal ranges
I want to Sum for Values Greater than a specific date in my formula.
This first one works for a specific date (Relevant reference in red), so say 01/01/2014
Code:
=SUMIFS(Table2[[#All],[Basic]],
Table2[[#All],[Cluster]],Table3[[#Headers],[Company]],
Table2[[#All],[Start Date]],Table3[#Headers],
Table2[[#All],[Perm / Temp / Prof / ETA]],[@Company])
Code:
To Sum for ranges >= 01/01/2014, I have tried this below based on the thread above but Excel does not accept this.
=SUMIFS(Table2[[#All],[Basic]],
Table2[[#All],[Cluster]],Table3[[#Headers],[Company]],
">="Table2[[#All],[Start Date]],Table3[#Headers],
Table2[[#All],[Perm / Temp / Prof / ETA]],[@Company])
I am trying to search a column for specific values.... if the cells in the column equal X, Y, or Z then I want to keep them. Anything else, should be deleted....and not just the cell the entire row that it is in.
So I'd like it to search column E for a number of different values, if equal keep the data. If not (say E6 is not any of those values) then delete that row (all of row 6)
I want to divide the numbers from 2 cells, down a long column. Let's further say that in some instances there aren't any numbers in either of the cells. You get a divide by zero error.
So you have values in a1 and b1 through a10 and b10. You want to divide the value in the a column by the value in the b column and return the answer in the adjacent c column. You've copied the formula all the way down to c20.
All the cells from c11 to c20 will have divide by zero errors.
What is the syntax to check if both cells in columns a and b are greater than zero so that either an answer will be returned or just a blank cell will result, (when nothing is in one of the cells from a or b?
The following formula works for checking the status of cell A, how do I also check the value in cell B?
=(IF(A5>0,A5/B5,""))
something like?:
=(IF(A5>0 and B5>0,A5/B5,""))
I have cells that they're values should be equal B6=B11, B7=B12, B8=B13, E6=B14, E7=B15, E8=B16, I want a macro that if the values are not equal then the background should be RED and a pop up message "The values should be equal".
View 9 Replies View RelatedI am trying to check if the dates are equal with multiple if statements! Macro works great if first date is wrong, but if the 2nd or third date is not unequal it does not work anymore.
really tried to do it myself but unfortunately
I added an excelexample
Date in E3 is different (if you type in other date in cell b3 macro does work partially).
How a single-cell formula to check that 2 transpose arrays are equal.
For example, A1:A5 are {1,2,3,4,5}
AND
B3:B8 are {1,2,3,4,5}
Is there an array formula in C3 for example, that will check (i.e. say TRUE) if corresponding ranges are true i.e. check in this cell that A1=B3, A2=B4,...A5=B8.
I have these two subs in my thisworkbook module.
They do not want to work together.
Is there a way to incorporate the two of them?
T
he first 1 just checks to see if a cell is greater than 0 and colors the Tab green.
The first 1 is this:
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Dim ShArr As Variant
Dim RunMacro As Boolean
Dim sCounter As Integer
Within 4 columns, I have a series of:
Start Dates / Start Times / End Dates / End Times
I need to create a formula which will give me the sum total of the difference between the data sets.
For example, the time difference between 8am on the 20th March and 1pm on the 21st March is 29 hours OR 1 day and 5 hours.
I need a formula which will calculate this for me.
I've been racking my brain on this one for awhile, and I feel like I'm going the right direction but...
I have a list of dates like:
11/30/2007
10/31/2007
9/30/2007
8/31/2007
7/31/2007
6/30/2007
5/31/2007
or maybe
11/30/2007
11/23/2007
11/16/2007
11/9/2007
11/2/2007
10/26/2007
10/19/2007
10/12/2007
10/5/2007
9/28/2007
9/21/2007
9/14/2007
9/7/2007
8/28/2007
...and I need to determine if the difference between them is daily, weekly, or monthly. There are 79 of these lists, on 79 different sheets so I can't visually peg them as daily, weekly, or monthly.
The way I have been looking at the problem is to calculate the number of months between the last & first dates and see if the resulting answer (+1) matches the number of actual dates, and so on...
I am wondering if there is way to analayse date and time data in a way that would allow me to look at time differences.
I have two columns of data, one has the date and time an entry was made onto a client database, the other shows the date and time an amendment or deletion was made.
What I would like is a formula that shows the time period difference between the two entries.
So for example if one column shows 14/02/2014 14:26 and the other shows 14/02/2014 14:28 it would show 1 (minute) as the result.
I have over 1000 entries to look at and manually it is taking a looooooooong time!
