I'm looking for a formula that will look at a sheet of 1500 entries and list the 10 oldest dates held in the sheet.
Is there any way of doing this other than data/sort?
The data that I would need to pull would be the customer name, ref number and the start date. (So roughly, name is column A, ref B, and start C)
I need a formula on my "Problem Areas" sheet that will search and then list
from my "Overall" sheet the ten oldest dates (column O), that have not been
closed (column B), and do not match the text "Repair Clin" (column S). Each
row of data begins with the purchase order# (column A).
On the "Problem Areas" sheet it will only list the PO# and Date Opened.
I have this column, and consists of a collection of dates. I want:
1 - change the format of the dates of "03.01.2013" to "01/03/2013"
2 - arranging dates from oldest to newest and then sort them without repeating
01.03.2013
01.04.2014
01.06.2014
01.07.2014
[Code] ...........
What I want to do is return the oldest date to another sheet, but I only want to return the oldest date if the backlog value is higher than 0. So example below has oldest date with 0 as backlog value, I don't want to return that date, but go to the oldest one with an actual backlog value.
So I have the following raw data in Sheet1
[Code] .....
Now I want to put this raw data in Sheet 2
I use this formula to pull the data =SMALL(Sheet1!C3:C5,1)) but this returns the B4 value to me, which I don't want as A4 is 0.
I've been thinking about somehow using VLOOKUP or IF to sort this out but I am stuck.
I've got one very strange column of dates. The dates in the column are in different formats and probably the first thing would be convert them into one format.
They are, like...
04/07/2014 04:50
and
08/24/12 09:46:59
... type cells. The next thing would be sort them in some logical way not just by first number.
Column with dates.xlsx
What I am having now and how far I am, is...
They might be sorted in some way by month and date but not by actual year that is left out completely. It is just total mismatch and doesn't make any sense.
I have a list of dates going through several years of data - month ends and starts don't always coincide with calendar months. I need to figure out a way to get the oldest date of the previous month, for example. Tried vlookup but could figure out how to do it for one year only, hence not as applicable with several years of data.
View 9 Replies View RelatedI want to sort a column in Excel 2007 which is having dates. Normally, we use oldest to newest or newest to oldest option in sort & filter. But i am unable to find this option in my excel sheet. how can I find this option in Sort & Filter.
Note: I have imported this data column from Access 2007
I have two worksheets in which I am trying to get the blue bold data (not text) derived from a formula.
Main
A
B
C
1
Site #
First Apt Date
Last Apt Date
[Code]....
Apt Report (month/day/year)
A
B
1
Site #
Apt Date
2
1001
[Code]....
I have some restrictions:
I cannot alter the format of Main, except I can add columnsI cannot alter at all the data or format on Apt Report (I cannot sort, filter, pivot, add columns...etc)
Any way for Main to look at Apt Report and give me the earliest and latest appointment?
I have tried the DMIN function but am limited it seems to being able to change Main to accommodate it. I have tried an array with MIN(If but ran into issues with the cells on Apt Report that are blank...it would return those as the earliest appointment....albeit it is correct...but not what I need!!!
The Apt Report will continually be updated to, so I need to be able to search the entire Apt Date column.
Looking for a formula in excel, was thinking of an IF with AND or VLOOKUP?? I have a database in Excel that has
A B C
1 9/1/988/31/00RED
2 9/1/008/31/02BLUE
3 9/1/028/31/04GREEN
4 9/1/048/31/06YELLOW
What formula would I use to take a Date (lets say its located in A8) and look up if it is BETWEEN the dates in column A and B above that it would give the result of column C
Examples
A8 = 11/07/99 would give the result of RED
A8 = 03/02/03 would give the result of GREEN
I have two tables. A list of accounts and the other is a change log. I need to add a new column in table 1 where the value is the amount in table 2 for the correct account and correct validity period.
View 3 Replies View Relatedattached worksheet.
The worksheet has two sections, Extra payments and Loan Schedule.
If a date in the Extra Payments section is within a range in Loan Schedule, the enter its corresponding value, or sum of values in the Loan schedule under its respective date.
the attached sheet explains in detail.
How can I lookup the value depend on Which year in the period?. I have prepared clean data attached. I use date calculation to find how many days in the period and then divide by 365 day to count the year. But it doesn't work.
View 5 Replies View RelatedOn the attached sample file, I used this array formula to lookup fo the
"First purchase date" - {=MIN(IF((Sheet2!A:A=Sheet1!A2),Sheet2!B:B))}
However, for the "Second purchase" and "Third purchase", i'm stuck and can't think on how to return the dates.
I try to find the price between two dates . I used lookup function, but couldn't get the correct result.
See detail at attached file and explained what exactly wanted : rates.xlsx
My lookup formula is not hitting the correct period. How can this be adjusted without changing any of the "GRAY" data.
View 2 Replies View RelatedI have a bit of code (See below) which basically looks at a list of data in rows 10:99 and then finds a date that is between cells c1 and b1. If there is one the it puts that date in if not it leaves cell blank.
[Code] .....
The problem I have now found is that is the dates in the sheet TR rows 10:99 and one of the dates is outside the range so for instance if the range set by c1 and b1 is 10/3/14 - 20/3/14 and in TR there are dates:
12/3/14
08/3/14
it wont return anything but if the dates are
12/3/14
08/3/14
15/3/14
it will return 15/3/14.
Is there anyway that I can change it so it wont return 0 if the last one is outside the range.
I'm looking to create a small section of VBA code on Excel 2007 that looks for a specific date on a column and then pastes a value on the correct row.
I have weekly dates in columns along the top of my spreadsheet on row 3:
C3 = 07/11/08
D3 = 14/11/08
E3 = 21/11/08
etc...
The row further down the spreadsheet that I wish to use is selected by previous code and could be any row number within column B.
I need a snippet of code that searches for a particular date along row 3, and then once it finds the correct column, it pastes the value in the corresponding cell on my active row.
For example, if my active cell/row happens to be on B18, and the date I'm looking for happens to be on L3, I need to paste a value into cell L18.
I imagine there needs to be some form of HLookup, but I've no idea how to implement this in VBA code.
i have tried: =VLOOKUP(TODAY()-7,A2:M2000,3) to return data that corresponds to a date 1 week ago. Unfortunatley, it does not work because my dates in the array are in descending order. The order cannot be changed... is there another function I can use to return the value I am looking for?
View 4 Replies View RelatedWithout VBA, I'd like to find the most recent date in a workbook. The workbook as various columns with dates on numerous sheets. I just need to know the date that is the newest date. Nothing else.
View 4 Replies View Relateddate1/Jan2/Jan3/Jan4/Jan5/Jan6/Jan7/Jan8/Jan9/Jan10/Jan11/Janquantity 502023108948202535current stock 200oldest date of manufacture?
I have a report that lists the quantity of an item that is manufactured on particular day. On some days there will be no items produced. The report is similar to the above table. I don't want to change the table format.
I know the current stock of that item (in this example it is 200). Note that the current stock is not just the sum of the quantiy for each day's production.
I need a function that will determine the oldest date of manufacture of material in the current stock. The function assumes that that the current stock is made up of the most recently produced material. ie the stock is depleted on a first in -first out basis. Essentially the calculation has to sum backwards from the most recent manufacture date until the total = 200 and then lookup to corresponding date.
In the above table the oldest prodcution date would be 6/Jan. The answer to the funstion in example = 6/Jan
sort attached file data (Dates Month Year) by ascending order.
View 10 Replies View RelatedI've posted versions of my complaints log/database here recently and am looking at the next version in reports.
I would like to create a small table that upper management can look at weekly and say "these are still open, why?" rather than having to look thru the log or go by memory.
I already have an "at a glance" chart, now I'd like to have something similar as a list (complaint number, date opened, customer, and maybe the issue at hand). would love to have this as a floating object that can be dragged around if needed. (embedded in a text box?)
Is there a way to pull out the oldest date ((04) Apr 25, 1950) and the newest date ((02) Feb 14, 2050)...?
i have tried the Min function (min(P28:P31)) and the Max function (max(P28:P31)) both resulting in a "0"..
i have also searched this forum as well as others and found formulas that were posted by Aladin Akyurek..but those did not work either..
Sheet12
P28(02) Feb 14, 205029(04) Apr 01, 201330(04) Apr 25, 200931(04) Apr 25, 1950
Spreadsheet FormulasCellFormulaP28=IF(O28=FALSE,"Who Knows?","("&RIGHT(O28,2)&")"&" "&LEFT(O28,3)&" "&LEFT(RIGHT(O28,4),2)&", "&LEFT(RIGHT(O28,8),4))
Excel tables to the web >> Excel Jeanie HTML 4
Im Using Windows Xp With Sp2 With Microsoft Excel 2003
I would like to automate a sheet to report statistics of projects based on a single cell that is the year that a user manually inserts. I've got a separate worksheet with all dates manually set up for next 10 years and would like it to be read by formulae in a reporting worksheet based on the year that user enters.
So if I specify year 2014/15 (financial year), I would like the cell C1 under "Reporting" sheet to display 1/07/14 predetermined in "Dates" B3, if I specify 2015/16, the cell C1 will read 1/07/15 from "Dates" B6 and so on.
I could do it with "IF" function but there will be too many nested IF functions in a lot of cells (hundreds if not thousands). This may make my file size very large.
Attached is a simplified example of what I mean.
I want to know the macro code to find out the oldest date in the Calculation sheet of the attachment based on two criteria.
The criteria should be if the Item ID on Calculation sheet matches the Item Id Pivot sheet and then if the Status on pivot sheet and calculation sheet matches then the macro should find out the oldest date for each item ID. And once the oldest date is identified the difference on pivot sheet should be added to the cost basis column on calculation sheet to the already found oldest date lot.
Find the attached excel sheet : Book1.xls
I am writing to a text file and only want to allow 10,000 lines. There is only one value on each line. How can I trim the top of this text file to allow for more entries to be appended to it and still keep it at no more that 10,000 entries?
The lines look like
"1001-1"
"1002-3"
"1003-3"
"1004-4"
"1005-5"
"1002-6"
"1001-10"
"1004-7"
"1015-5"
"0002-6"
I inted to invert stock quotes from newest to oldest but I can't cope with it.
How to invert for example numbers like 2, 10, 3, 15, 1 into the form of 1, 15, 3, 10, 2 ?
I have 4 columns (Date (A), Open (B), High (C), Low (D)) in date order with the newest at the top. I have a value in G1, I'd like to see the oldest date returned in H1 of when a value in the (D) column is lower then G1. I've been looking at match and vlookup but what I can't seem to figure out is how to return the oldest date.
View 8 Replies View RelatedI am looking for a formula to determine the oldest date in a range of cells. BUT, the part that's kicking me in the pants here, is that the range will change.
In column D, I have a person's name. The number of times that person's name will appear will change every time I run this formula. In column F, I have a date & time. I'm looking for a formula that will determine the oldest date/time associated with that person.