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.
to change the format of some dates that are imported as text.
In a previous request ...
[url]
I was shown a way to convert dates correctly.
This solution works perfectly when carried out manually, but doesn't work if a record a macro and then replay it - only some of the dates are converted.
Here is a small sample of the dates I'm working with...
04/09/2008
05/10/2007
08/10/2007
14/09/2007
15/09/2007
17/09/2007
17/09/2007
17/09/2007
19/03/2008
20/09/2007
21/09/2007
21/09/2007
25/09/2008
27/09/2007
28/09/2007
28/09/2007
And here's the recorded macro, deliberately left exactly as is
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 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'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’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 have 2 worksheets - one is a monthly update, the other is last month's data.
I want to highlight the changes on the monthly update sheet using conditional formatting.
I named the columns as ranges on the "last month" worksheet, and then used =Match(A1,Jan,0) (wheras Jan is the range on the "last month" worksheet where A1 resides)
What formula do you use to say "if A1 does NOT match a value in range "jan", then format it"?
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 would like to compare each cell in a spreadsheet to the cell on its right.
If the numeric value of a cell is less than the numeric value of cell on its right, I would like to highlight the cell on the right light red.
Maybe a better way of putting that is if the value of a cell is greater than the cell on its left, highlight it light red (The problem I foresee with this formulation is in the first row where there are no cells to the left).
these are two tables i have to compare.Sl No
Type
1
NORMAL
2
NORMAL
3
NORMAL
4
QUAD
[Code] ........
Sl No
Type number
1
1
2
1
[Code] ........
where,
1=NORMAL
2=QUAD
3=BI-DIRECTION
i want to compare between these two table based on the condition writen in RED. IF there is a mismatch i want to highlight the values in the first table.
I'm working on the final stage of a project. I'm attempting to write code that will set off a chain of events if two dates are 91+ days apart from each other . I've attached a sample worksheet that shows the bare bones basics of what I'm attempting to do.
What will be the most efficient and effective way of accomplishing my goal: userform, functions, formatting, etc.? T
I need to VBA code that will loop through the active sheet for all rows, column C2 through however many rows there are. Col C contains a date in the format: mm/dd/yyyy. I need the VBA to compare this date to today's date+30 days and if the date in C2, etc. is greater than today's date + 30 days, shade the row a particular background color (e.g., 3).
[URL]
take a look at the attached example and let me know how to make the formula work?
For each "title" in column A, I want to compare the two sets of start and end dates (columns B & C versus D & E) and if the date range in column B &C overlaps with the date range in column D & E, input "yes" or "no" in column F.
In my attached example, rows 8,9,11, and 12 would be populated with a "yes", the rest would be "no".
I'm trying to write a macro that compares 2 coloms(open,close) of dates/times and calculates how long each was open for. This is what I have so far. I've also include as an attachment an example of the date/time format i'm using.
Sub Time()
Dim A, B, C As Long
Sheets("Sheet1").Select
For i = 7 To 5000
A = Fix( Range("A" & i).Value)
B = Fix(Range("B" & i).Value)
C = A - B
Cells(C, i).Value = C
Next
End Sub
look at the attached file - it was a CSV file. i want to convert the column of dates to say Mar 14 2009 type date. but it only converts some of them.
note some are on the left and some on the right.
The attached workbook has dates in column C, although some of these dates are just strings.
I'm trying to write some vba that will tell me how many of the cells in column C contain a date (or looks like a date) that is greater than (after) the real date in cell G1.
At the moment I loop through the cells in column C and can ascertain, which dates can be counted, then copy one row over at a time, but I'm looking for a slicker (perhaps one-liner) answer, perhaps by copying a block of rows in one go. The aim is to copy those rows to another sheet. There are many more rows than in the attached, and many sheets to process, and I have no control over the format of the dates/strings in column C. Currently it takes about 20 seconds to copy over the necessary rows, but I'm looking for it to happen much more quickly; current thoughts are to sort on column C (sorting on column C anything that looks like a number as a number - which has it's own problems!), have a count of dates satisfying the criterion (say using a worksheet formula such as COUNTIF or SUMPRODUCT, perhaps also using EVALUATE) then copy a block of rows in one go.
not very relevant, but the existing code is something like this which highlights rather than copyies the rows(included in the attached): ...
A little background on what i am doing. I have a spreadsheet that tracks when i have blown the dust out of our computers. I have set up conditional formatting so that the text turns red after 300 days and the cell turns yellow after 600 days. However, some computers are in high dust environments. I would like the spreadsheet to tell me to blow the dust out of these sooner. see the attached sheet.
dust 1.xlsx
What I am looking for is a macro/formula that will look at a unit number, then the type of work done, then the completion date and decide if the same type of work was done on the same unit within a 7 day span of time.
Example
Unit is 11111, work is plumbing completion date is 10/4/07
Unit is 11111, work is plumbing completion date is 10/7/07
This means the work has been done 2 times (obviously showing that it did not get fixed the first time)
Is there any way to mark an X in the square with the formula, or any type of identifier what-so-ever
I have attached a spreadsheet and hope it helps
Again I know this is complex so it is OK if no one feels like messing with it but I wanted to check here first.
I need to compare three cells of random dates shown in Column E, F,& G with Row's H5:AK5, H7:AK7, H9:AK7 (the Dates to these rows is Static on row H3:AK3.) EX: ROW 5 has Start Date, End Date (1) and End Date (2). Compare Cell H3 between Start Date & End Date (1). If H3 falls between or equal to Start and End Date(1) then highlight cell H5. Proceed till AK3 (higlighting only the cells H5:AK5). Then compare cell H3 between or equal to End Date (1) and End Date (2) (higlighting only the cells H5:AK5). Then do the same for ROW 7 and ROW 9.
To make things a bit more difficult I need to have " WK#" in Row 14 (these WK# is on another tab called "Task" of the workbook) needs to be displayed in Row's H4:AK4, H6:AK6, & H8:AK8. EX: Compare Date in D15 between or equal to Start Date & End Date (1) then display Wk# in D14 in H4. Continue till all dates in
D15:Z15 are compared to Start Date & End Date (1) and WK#'s in Row D14:Z14 are inputted if applicable in Row H4:AK4, H6:AK6, H8:AK8. I hope this is not confusing. I can't seem to use the upload option so here is alink to download a jpg of the sheet
I'm using Excel 2010. I need to populate a daily calendar with the number of nights spent, extracted from the Date of Arrival and Date of Departure of individuals.
View 2 Replies View RelatedIn the expense log, Column C is a list of Dates and Column I is a list of expenses. I want to Sum the expenses in the 'Expense Log 09' to a new sheet based on a Date entered in H24 on the new sheet. I have tried the formula as shown below and Get the result #NAME?
=SUM(IF(Expense Log 'Expenses Log 09'!C8:C100,H24,'Expenses Log 09'!I8:I100)). I would Like to SUM all expenses After the posted date including that date.
I am trying to do conditional formatting to make a cell say something for 3 different scenarios. If K6 > G6, then I need M6 to say "Early." If K6 < G6, then I need M6 to say "Late." If K6 = G6, then I need M6 to say "On Time."
View 7 Replies View RelatedIn my attached file,
In Column Q, Please drive a formula that shows.... after 7 days of column (M)reminder III these words must appear '' File Pending'' in 'COLUMN Q' and at the same, Indicator column (O) will also become RED.
Test.xlsx‎