I have to detect all staff members with the passport expiration dates between two given dates, i.e.
A 12/4/2012
B 5/3/2012
C 7/3/2012
D 6/3/2012
E 9/12/2012
F 12/11/2012
G 11/16/2012
H 10/12/2012
etc
So, I need to have excel detect all staff members with the expiration dates between let's say 9/1/2012 and 12/31/2012.
I have a problem on matching up two sets of data. One set has a start date/time, and an end date/time. The second set has a single date/time.
Set 1:
Process, Start Date/Time (dd/mm/tt hh:mm) and end Date/Time
e.g
Process 1, 01/01/2009 12:45, 01/01/2009 12:56
Process 2, 24/01/2009 13:45, 24/01/2009 14:02
Process 3, 24/01/2009 14:38, 24/01/2009 14:43
Process 1, 01/02/2009 12:55, 01/02/2009 12:56
Process 2, 08/02/2009 12:35, 08/02/2009 15:21
Set 2:
Event, Date/Time
e.g.
Event 1, 01/01/2009 12:55..................
finding a formula to calculate the number of months between two dates. I've attached an Excel spreadsheet with sample data. Some are in the future, and some are in the past.
Excel Help.xlsx
I was wondering is there a way to find all the dates in column A and if they are two or less days then the system date then change the font to bold?
I have an excel sheet which contains date in a cell .I need to find the date in sheet and keep it in a variable. I came across this code which finds dates but it prompts the user to enter a value and then searches.But i need the macro to run automatically and find any date present in the sheet without any prompt for the user.
Sub FindDate()
Dim strdate As String
Dim rCell As Range
Dim lReply As Long
strdate = Application.InputBox(Prompt:="Enter a Date to Locate on This Worksheet", _
Title:="DATE FIND", Default:=Format(Date, "Short Date"), Type:=1)
If strdate = "False" Then Exit Sub
strdate = Format(strdate, "Short Date")
On Error Resume Next
Set rCell = Cells.Find(What:=CDate(strdate), After:=Range("A1"), LookIn:=xlFormulas _
, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
On Error Goto 0
If rCell Is Nothing Then
lReply = MsgBox("Date cannot be found. Try Again", vbYesNo)
If lReply = vbYes Then Run "FindDate":
End If
End Sub
I am having difficulty finding the difference between two times and dates in Excel. I have researched this on Google and tried several formulas but none have worked for me. The format the data is currently in can be seen below:
A1: 1/3/2014 11:00:02.230 B1: 2/7/2014 08:42:37.637
A2: 2/12/2014 07:51:58.663 B2: 2/13/2014 05:36:31.893
(Note all times are military time format)
I want to display the difference in C1 in a format along the lines of "dd hh:mm". The data for seconds and fractions of a second is included in the source data but is not needed in the final results.
If the date and time falls on the same day, I have no trouble doing a simple "B3-A3" to determine the answer. For example:
A3: 2/5/2014 09:56:06.273 B3: 2/5/2014 10:39:51.34
Gives me an answer of: 0:43
Trying to do the same for A1 and B1 gives me this: "03 21:42" when I format it to "dd hh:mm".
Is there a formula that would give me the correct answer in both of these cases?
Finding an average age from a list of dates. This is just a small sample:
7/9/2013
7/10/2013
7/10/2013
7/10/2013
7/11/2013
7/11/2013
7/14/2013
7/14/2013
7/19/2013
7/19/2013
I tried using =AVERAGE(DAY(A1:A10)) CTRL + SHIFT + ENTER, but that just doesn't seem correct.
I have a workbook with two sheets.
Sheet1 contains details of all of the jobs I do.
Sheet2 is my customer list.
Customers appear only once in sheet2, but may appear mutiple times in sheet1.
In sheet1 I have customer name, lead date, job date et al.
In sheet2 I have customer name, first date, last date et al
I am trying to populate first and last date in sheet2 from the lead date and job date in sheet1.
Sheet1 has a range of 500 jobs
find the difference between 2 dates in excel "date 1 - date 2"..... but I'm not quite sure as to how one would do it while excluding holidays!
View 8 Replies View RelatedIm trying to search a column (A), that has a list of dates (not in order), for the row in which the dates are equal to or greater than today and less than or equal to a week from today. I then want the information contained in the rows with these dates to be transferred to another sheet and ordered by date.
i have with noting the most recent date of a test and also the one prior to the most recent dependant on location and test type. I require most recent and previous test info to enable a comparrison to be made. All other dates are not required but need to be kept as historical data and can't be deleted.
Is there anyway i could get column E to auto populate and amend itself as more test dates and locations are added to the list?
Within a user entered range of two dates, I would like to identify the individual calendar date(s) and count the number of Mondays which fall within the specified date range.I will eventually be using the same "Monday" code to find the same data for every day of the week within the dates ranges, but I figured I'd start with Mondays and build from there.
For Example: Date range 1/1/2013 - 1/15/2013 (date ranges could potentially encompass a full business quarter) Within the range, list each of the dates as dates. (used for comparative counting purposes elsewhere in the document)Count the number of Mons, Tues, Weds, Thurs, Fris, and Sats within the date range.Based on the example date ranges above; Mons = 2, Tues through Sats = 3 each.
I have a pivot table and and struggling to group these by month as well as to sort thee in escending order.
Pivot Table ABC3Row LabelsSum of DebitSum of Credit
413/02/201334367.1822844.19513/03/201326475.492219.66613/08/201230307.613541.2713/09/2012
18898.0318065.4813/10/2012 7210.52913/11/201241969.041767.821013/12/201232844.7724041.26
I am working on large sets of data (more than 50,000 rows of data). I have two sets of data. Set 1 and Set 2 (master data) on the same worksheet. Both the sets of data have three columns each. I am using EXCEL 2007. I was able to accomplish step 1 below.. but I am totally lost with step 2 since i have an additional criteria for the "year".
I have attached the excel sheet as well. This is what I am trying to accomplish:
1) I want to find exact matches in set 1 and set 2 and highlight it or do something to show that a match was found. The challenge is the data in set 1 can occur anywhere in set 2.
2) Add to the complication .. my criteria for matching the year is different. If the Set 1 "year" is equal to or greater by 1 yr or greater by 2 yr when compared to Set 2 "year", I want to treat it as a "match".
For example, from the data attached:
Set 1 data in row 4 is: ATLANTIC ABSECON 2004
Set 2 data in row 3 is: ATLANTIC ABSECON 2003
I want to treat these two data as "MATCH" since ATLANTIC matches ATLANTIC, ABSECON matches ABSECON and according to one of my criteria for year, Set 1 "year" is greater by 1 yr than the Set 2 "year".
How would i write VB code that looks for the last row in my data table? I'm a total novice with VB but learning slowly..
View 9 Replies View RelatedOn my example sheet it shows how many cables and how the cable length per job. What i need is for the hour/job column to be populated from the Hours sheet by matching the column and row. e.g 4 cables at 40 metres would give me cell D7 (27.89), as 25 metres would be no good - so rounding up would be required.
View 2 Replies View Relatedassist me with editing this code so that even though my range may be set from A1:A3000 it would stop after finding and completing its task in the last cell in column A with data. This code will put dashes in a set of numbers and there is no certain amount of account numbers that could end up being in column A on any given month.
View 3 Replies View RelatedI have a range that changes the data constantly, I have to watch that data changing. I am trying to work on a macro that copy that data and paste to another sheet. What would be the code to find the next empty row and paste my data there.
Data is in Sheet1, range A17:E32... and it needs to be pasted in sheet2 starting in F2.
My spreadsheet consists of two columns, column A holds a series of numbers whilst column B, again holds a series of numbers but with an added single letter on the end.
What I would like to do is find a function which will match the numbers and letter in column B with the corresponding numbers in column A and then place the result in column C ....
There is a data where values of indicator (in this case Gini coefficient for different countries) is not available for all years - some recent ones are missing - see the picture. What is the most efficient way to find the most recent data in a row?
View 1 Replies View RelatedIs there a way to find data in an array similar to the way match, index or vlookup is used for data in cells of the spreadsheet?
Example, if I have a 2 dimensional array:
1 apple
2 orange
3 pear
4 banana
5 grape
and I want to know if the word "banana" exists in the second column of the array, is there code that would determine if it was in there at all, and is there code that would determine that it was item 4 in that array?
I have a set of data which are deal numbers
So week 1 i could have
Deal
ASD
ADF
AGF
Then next week i would get another list of deals
ASD
AHJ
ADF
AGF
AIG
Is there a formula i can use which well tell me if a new deal has been entered in the following week.
So in the above AHJ and AIG would be new deals
(i would simply copy the deals and put them in Column A and B and the formula to go in column c)
I have a simple problem and I'm sure someone out there has the simple answer i'm looking for. Here's my problem:
I have a column of cells with data in them (no formulas, just "hand-written" data). I would like to find the last cell in the column that has data, but without using VBA.
I tried to find help, and I thought I might be able to use the LOOKUP formula, but since I never used it before, I kinda hit a wall... is there any other formulas that might do the job? or do I absolutely have to use VBA?
After I find the last cell with data, I will use it in another formula in another cell... nothing too complicated, but I need (if possible) the data in the cell to be returned to the other cell for the other formula.
I am a basic user of Excel.
I have an Excel spreadsheet with data from the last 10 years.
In one column it has date in dddd,mmmm,dd,yyyy format and the second column with data for the dates.
I need to have Excel extract only Tuesdays with data that is a positive number (above 0).
Is there a formula, or other method of finding only these results from the whole speadsheet?
The list of data is shown in sheet1
In sheet2 I have shown the data that is copied
I have manually shown comments on 2 cells in sheet2 that shows detailed description on how was the total taken.
I need the comment on all the cells from D8 to G11 (not taking the total column) that shows on how was the total taken.
When any entry in sheet1 is inserted or deleted the comment in sheet2 should change accordingly.
I also do not want to freeze the cells D8:G11 as when new entries are made or deleted the columns would increase or decrease.
All blank cells I do not need any comment.
I have two spreadsheets of "leads"
One lead is a do not call list that has 4 separate columns of phone numbers,
The second list is a batch I purchased.
I need to make sure that the second batch does not contain the info from the first spread sheet.
I guess I could modify the 1st spreadsheet to just be one column of numbers, then I would be comparing one column to one column.
Is it possible, and does anyone know the formula to find someones name and add the data to the right of it? Example
A1 John B1 32:26:00 C1 500
A2 Bill B2 23:45:44 C2 452 D2 John E2 36:45:11 F2 520
I have many names and they are scattered throughout the sheet. I know how to add them if the are in a single column but not if they are scattered.
In my summary I would have the names ...
I have a table (Sheet 1) with four columns data, A,B,C and D. There are about 60,000 entries in them. In Sheet 2, I wish to enter a value in A1 which will be from A OR C columns of Sheet1 and get its corresponding value from B or D (Sheet1) in B1 (Sheet2) with the help of a formula. i.e. IF(A1, Sheet1!A60000:C60000, then B1 = B or D of Sheet1).
View 2 Replies View RelatedColumn A = Date
Column B = Numbers
Column C = results
As you can see Column C, I manually did what I want the formula to do, which is get last number from the previous week. (weekdays only, but can be either 1,2,3,4 or 5 days due to holidays).
Any way of finding particular data from a data set and deleting the data from that field without using conditional formatting or remove duplicates?
I have used a vlookup to find the words that I needed I now need to find where the are located in my original data set and delete these so I am left with data that if I perform a vlookup on it will not bring back any words as they will have been deleted.