I have trouble trying to get the right sintax of comparing two dates locates in two different sheets.
Example:
Sheet 1, cell A1 = 12/11/2009
Sheet 2, cell B1 = 12/11/2009
If I need to do something based on condition that Sheet1 date = sheet 2 date, how will the code be written? I have
If Sheets("Sheet1").range("A1") = Sheets("Sheet2").range("B1") Then
**** my code here
I am using excel 2007, and trying to incorporate a VLookup into some code. The VLookup is comparing dates, which I think may be part of the problem.
View 3 Replies View RelatedHow to compare two dates in excel. Like my requirement is I need to compare curent date vs from month begin date two weeks date.As of now I have tried like this.
Current Date : =TEXT(NOW(),"d")
Month Begindate : =TEXT(DATE(YEAR(NOW()),MONTH(NOW()),1)+14,"d")
And I have written condition like this : =IF(TEXT(NOW(),"d")
Formula for comparing two dates
Cell A1 = 5/5/2012 (Recent Date)
Cell B1 = 5/6/2011 (Hire Date)
Cell C1 = =YEAR(A1)-YEAR(B1)
This formula gives a result of '1' even though it doesn't show an entire year.
I've also tried this formula:
=DATEDIF(B1,A1, "y")
This is more accurate as it formulates '0' as the result.
However if for some reason the 'Recent Date' in cell A1 is 5/5/12 and the 'Hire Date' in cell B1 is after the 'Recent Date', say 5/6/2012, the formula =YEAR(A1)-YEAR(B1) has a result of '0' and the formula =DATEDIF(B1,A1, "y") has a result of #NUM!.
Is there a formula that will give me accurate results whether the 'Hire Date' is before or after the 'Recent Date'? Maybe something similar to =DATEDIF(B1,A1, "y") but will give a result of '0' instead of #NUM!
I have a comments field in a reporting SS that I need to flag when the last comment is over a week old. The date however is entered into the same cell as the comment and the latest comment is put on a new line in the same cell, ie:
13/02 - Jimmy met with Shirley to discuss project A
10/02 - awaiting meeting
07/02 - agenda issued
Now, if needed I can make the date complete to include the year, ie 13/02/2008.
Is it possible using functions to see if the date in this cell is over 7 days previous to the date in another cell, say J1?
I tried =IF(LEFT(H10,10)
I'm trying to determine if one date meets the criteria to be considered 'on time'. I have two columns: estimated completion date and completion date. I want to compare the completion date to the estimated completion date and if completion date is <= completion date but is NOT = 1/1/2099 then I met my date. I've tried this several different ways and problem is I cannot make it work.
When estimated completion date is 1/1/2099 'on time' should be 'no'
When estimated completion date is not 1/1/2099 but is greater than completion date 'on time' should be 'no'
When completion date is <= estimated completion date and estimated completion date is not equal to 1/1/2099 'on time' should be 'yes'
And I have a null date to deal with also but if I can satisfy the first 3 criteria I can manipulate the null values manually.
I have a problem regarding month formatting
I have 12 columns, Jan 2013 - Dec 2013.
I need to set a format in which if I enter a num eg 15 on the Jan Column, format comes as "15-Jan" format
or If i type 20 on the march column , it comes as "20-Mar" format
I would need it in Date format, because I would be comparing dates later on.
I’m attempting to find values that are within a band of .001 of the values of cells in columns L through O and searching columns B through I for values that meet the .001 criteria and copying the values to columns Q, R, S, and T respectively.
I mentioned the values in L, M, N and O are where the comparisons will be made and these values are tagged to a specific date in Column K. I need to find the dates in Column A that are in between the values of K and the cell beneath it. For example, in K4 I have a date of 10/3/99 and the next date I have in K5 is 10/5/99. I would then query Column A for the dates 10/06/99 (A4), 10/07/99 (A5), and 10/08/99 (A6). The values I need to find within the .001 band are in Columns B through I (B4:I6). Lastly, when reach the last value in Column K, query all values in Column A greater than last value in K and copy the appropriate values in B through I to Q through T.
Rules for copying data to Column Q, R, S, and T:
For Column Q-
Find values in B, C, or D that’s within plus or minus .001 of M and copy the value of B, C, or D into Column Q of the same row.
For Column R-.................
Any easy formula to compare two columens of fixed dates and count how many of each fall within a certain range of each other.
So, for example:
Col 1 Col 2
20/1/11 21/1/11
20/1/11 31/1/11
18/1/11 24/1/11
Lets say col one is a start date and col 2 is an end date. Now say I want to count the number of times the end date falls within8 days of the start
I would have thought that something as simple as
Code:
sumproduct(--(a1:a3<(b1:b3+8))
would work, but clearly I am making a fundamental error, and no matter what I try I'm getting stuck.
In functions it is easy to simply say =if(a1>b1, do X, don't do X)
But how do I do this in VBA? Excel 2007
I want to be able to compare a user-input date to dates in a column, and return 1 if the date in the column is less than the user input date, and 2 if it is greater.
Code:
Dim TheString As String, ReportDate As Date
TheString = InputBox("Please enter the date this report was run in mm/dd/yyyy format:", "What date do you want to enter?", "mm/dd/yyyy")
If IsDate(TheString) Then
ReportDate = DateValue(TheString)
[Code] ...
Code:
ActiveCell.FormulaR1C1 = _
"=IF(R[-1]C[23]
I have a simple formula to compare dates but it isn't working. I am missing something obvious. Any help is appreciated:
IF(BB64=Current_Date,"Today","Previous"))
where
Current_Date = today()
BB64 = an array that is a feed from a streaming quote company: =ILX|Q!'f,Date' .
When I format this cell it looks exactly like Current_Date, but is displays "Previous". Somehow it is not doing an apples to apples comparison even though the values lookthe same.
I need to compare dates that include both the date and time of day, to find when the day changes.
I have a large list of dates (also includes the time of day in the same cell). There are specific values in the next column that correspond to that date. Some of the values that are too great and have to be changed. This I have figured out with some help from this forum. When a value is changed I need to add together all the values for the corresponding day, some could be above and some could be below.
I have two spreadsheets I would like to compare against each other, last weeks inventory (Sheet 1) and this weeks (Sheet 2). All items are listed by unit number, is there a way to have all unit numbers on Sheet 1 colored red that are not on Sheet 2 and vice versa?
View 5 Replies View RelatedIs there a way to compare two cells and return true if they are the same, false if different? There is text and numerical data. As a side note, can anybody recommend a lightweight reference so I can sit for an evening or two and at least get an idea of what excel can do?
View 3 Replies View RelatedI need to compare a value to several cells and print the value into a new cell if and only if the value is not in any of the cells I am comparing to.
Say cells A1, A4, A8 and A12 contains different numbers. I want to compare a value, say 120, to the values of the above cells and if it is not contained in one of those cells I want to enter 120 into cell A16. If A1, A4, A8 or A12 contains the value 120 I want to leave A16 blank or write 0 to it.
Is there an easy way of doing this? Right now I am using a if-test for each of the cells I am comparing to, but this gets very messy as the number of cells increases.
how to compare two cells (say, today's date with the date an order was supposed to be sent out), with a third cell that either has a tick in it, or nothing. If there's an easier way to do this other than VB code, I haven't found it. I tried conditonal formatting but it wouldn't let me have a condition in which it just checked the third cell, it needed to compare it with the selected one.
View 9 Replies View RelatedI have two excel files. The data is arranged by column. I want to take 3 or more cells (from three different columns) from a particular row and match them up with three cells (in 3 different columns but in the same row). The three cells should be in the same row. However the matching row may appear in a different order in each of the two lists. When Excel finds a match, I want it to return true. How can I go about doing something like this?
I have attached a file explaining what I mean. I am trying to match the three cells in list one with three cells in one of the rows in list 2. However the number of cells in each list is different and there are some cells in list 2 which are not in list 1 and vice versa. There may also be extra columns, which I have not included for the sake of simplicity.
If u dont mind can i have example xls file please.
i hope u guys will help me to improve my knowledge by uploading attachments ...
I have column a,b,c. b and c both contain numbers. if cell c1 equal b1 then i want "new" to be displayed in a1. If it does not equal then i want "old". but if c1 is blank then i want a1 to remain blank.
View 3 Replies View RelatedI would like to make a macro which compares the content of the same cells in two worksheets which are in the same workbook.
More clearer I have a workbook with two sheets (Sheet1 and Sheet2). What I would like the macro to do is to check cell A1 in Sheet1 and compare it to cell A1 in Sheet2 and so on till the last cell (IV65536). If there is a differnce between the two cells, then it should highlight the background of the cell in Sheet1.
I know that there has been similar requests on this forum (I searched and read them) but thew were different.
I was wondering if someone can at least point me in the right direction with respect to comparing two numbers within a value in two different cells. For example:
If one cell has the value "AB123456"
And the cell below it has a value of "AB124658"
I want to be able to determine the difference (with VBA code) between both cells by just looking at the fourth and fifth characters ("23" and "24", respectively) in each cell value.
I have a spreadsheet that has long text sentences in each cell. I have a 2nd spreadsheet which is a slightly updated version of the first spreadsheet. The slight updates consisted of editing the odd word here or there out of the first spreadsheet.
Unfortunately, I didn't keep track of the changes I made, and I need to know what they are.
It's easy to tell if a change has been made, simply by comparing the cells. But I can't find an easy way to find out exactly what change was made. Comparing the cells a line at a time is very time consuming.
I have a Workbook with two Sheets MAIN and STATS
Col A in both sheets contain text values (names)
I need a macro which will:-
Look at each name in Col A MAIN sheet.
See if there is a match in Col A of STATS sheet.
If there is a match then enter "YES" in Col E
e.g Smith Eddie is is in the Cell A20 MAIN sheet, Smith Eddie is in Cell A55 STATS sheet, so Enter "YES" in Cell E55 STATS sheet.
I would typically consider myself a decent Excel user, but I haven't been able to solve this one. Maybe it's just the lack of sleep now. I am attempting to combine 4 cells into one where the last cell contains a date. Below is the formula I am using:
=B3&" "&C3&" "&D3&" "&TEXT(E3,"m/d/yy")
It works when all of the cells have values, but the only problem I am having is that some of the dates (in column E) are blank. When this cell is blank the default date that displays is "1/0/00." If I add the typical, " " at the end it says the formula has errors. The need for the TEXT(E3,"m/d/yy") for the cells that contain dates is throwing me off.
I have a WAY to complicated excel form that I'm going to move to Access but I need to have this working in the interim. The form has a dynamic element, the user can choose the number of items they are ordering and it unhides the required number of text boxes. To keep the scripting down a did a little work-around to update the database using a loop through the form controls:
'Save the main row
MainOrderRow = ActiveCell.Row
LastRow = MainOrderRow + (ExtraOrders - 1) ' Calculate the last row
ExtraOrderNo = 1
For DBRow = MainOrderRow To LastRow
' Save the text box name with the extra order number
k = "txtDescriptionMul" & ExtraOrderNo & ""
l = "txtQuantityMul" & ExtraOrderNo & ""
m = "txtTotalValueMul" & ExtraOrderNo & ""
n = "ComboBoxUnitMul" & ExtraOrderNo & ""...................
Thing is, I have an excel sheet for a private competition in sports games. How can I do a function, that automatically calcutes points for every "bet" against the game result?
For example:
If game finishes 4-3 I'd like to compare it for every players guessed score and give points according to this:
1. If the bet is completely correct (4-3) -> Return 10 (points). In case of tie (like 3-3, -> Return 20 (points)
2. If the bet has a correct winner, with correct goals for eather team (4-x / x-3) -> Return 4 (points)
3. If the bet has a correct winner (home/visitor) -> Return 3 (points)
4. If the bet has incorrect winner but amount of goals for eather team correct -> Return 1 (point)
5. If the bet is completely wrong -> Return 0 (points)
At least for me, this sounds more like rocket science but just wanted to check if this is anyway doable.
I need to compare some cells, but sometimes one or two cells can be empty (but sometimes none of them)
e.g. A1 = January
B1 =
C1 = January
D1 = January
E1 =
and I need to compare this cells A1:E1 and if text in not empty cells is the same then write TRUE to F1 and if not write FALSE.
I have a list of authors for about 20 papers. These Papers are listed in sequential order from 1999 to 2005.
I want to track the number of similar authors from paper to paper. Thus I am attempting to create a macros that has the Papers listed sequentially when they were published and the authors for each paper, with the authors name in each cell. I want to create a macros that will compare the Author Cells associated to lets say the Fifth Paper with the Author Cells for the Fourth Paper (the Previous Paper). So that if certain authors come up in the Fifth Paper that were in the Fourth Paper, the # will be noted.
I also want this macros to compare all the Author Cells that have appeared in the past with that of the author cells of the (N) paper excluding the (N-1) Paper, and just note the # of occurences. So using the previous example. If we are talking about the Fifth Paper, it will look through Papers #1-#3 for any matching keywords and note the number.
I have attached my spreadsheet to make it more clearer. For some papers there are more than 20 authors as well just to note.
I am trying to find compare the value in one cell with an array of cells. My problem is when I try to carry the formula down to the next line - my array changes as well. For example:
Before
After
01915
[Code].....
So, if Before is Column A and After is Column G, the formula I have right now is =INDEX(A2:A3349,MATCH(G2,A2:A3349,0)). When I try carry the formula down to G3, G4 and so on, the array changes as well.