Return Oldest Date With Some Dates Excluded
May 30, 2014
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.
View 3 Replies
ADVERTISEMENT
Feb 28, 2014
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 Related
Aug 10, 2006
I need a formula that links to another workbook where it looks in a column for the oldest date which appears more than 10 times.
So if the oldest date was 01/01/2006 but it only appeared twice, it would ignore it, but if there was then 02/02/06 which appeared 11 times, it qould quote that date in a cell in the workbook
View 9 Replies
View Related
Mar 19, 2014
in Excel 2010, whether using VBA or otherwise;
1) how can I return the oldest date in the entire workbook (or at least in the entire sheet if it's not possible to find that date in the entire workbook).
2) how about the most recent date ?
View 2 Replies
View Related
Nov 16, 2005
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.
View 14 Replies
View Related
Oct 25, 2007
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)
View 7 Replies
View Related
Jul 24, 2014
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] ...........
View 7 Replies
View Related
Jun 11, 2014
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.
View 2 Replies
View Related
Jul 1, 2008
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
View 2 Replies
View Related
Jun 6, 2013
Without 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 Related
Dec 3, 2009
date1/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
View 9 Replies
View Related
Dec 29, 2009
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
View 9 Replies
View Related
Jun 4, 2014
I 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
View 9 Replies
View Related
Dec 24, 2012
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
View 4 Replies
View Related
Jan 30, 2012
I 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.
View 2 Replies
View Related
Aug 19, 2012
How do we sort an alpha numeric date from recent to oldest.
from this:-
HGH010112
HGH231211
HGH020212
to this:-
HGH231211
HGH010112
HGH020212
View 2 Replies
View Related
Oct 3, 2008
I am looking for a formula which will find the oldest date for a specific set of search criteria:
A1 A2 A3 A4 A5 A6
05JUL2008 06JUL2008 07JUL2008 08JUL2008 09JUL2008 10JUL2008
B1 B2 B3 B4 B5 B6
Cat Dog Pig Cat Pig Dog
So if possible I would like a formula that searches through and returns
C1 (Search for Pig) = 07JUL2008
C2 (Searcg for Cat) = 05JUL2008
C3 (Search for Dog) = 06JUL2008
View 9 Replies
View Related
Sep 10, 2009
I looking to track the oldest entry that is still marked "Open" or "In progress".
So far I have done the below If Statement:- =IF(H24="Open",E24,IF(H24="In progress",E24,"")). This returns the Date it was logged if the status is "Open" or "In progress". to column T, So far so good!
So in cell T22 i wanted excel to show me the oldest date so i used the MIN function like so:- =MIN(T24:T40). This returned me the oldest date which i then wanted to know how many days this has been open for so i used:- = SUM(TODAY()-H6)
which gives me the number of days. This all works fine until..........i don't have any entries set to "Open" or "In progress" or any dates added (e.g. start of a new week). When this is the case the MIN function returns a value of 01/01/04 which in turn tells me that the oldest entry is 38,604 days old. I have tried different combinations of If statements to ignore the blanks / date etc but i just can't make it return zero or even better blank. I have attached a sample of my sheet and inserted comments to explain what each part does
View 2 Replies
View Related
Jul 6, 2007
I have been trying to search for either a function or VBA that will perform the following:
If I enter I want all months >=1/1/07 and <=3/31/07 it would tell me that the months in between are January, February and March and so on with all dates.
View 3 Replies
View Related
Oct 26, 2008
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 Related
May 23, 2014
I have a UserForm which writes data to rows in a master spreadsheet. I'm attempting to write some vba code for a CommandButton in the master spreadsheet which can identify and delete duplicate entries based on "user ID", "Date", and "Time". I would like the CommandButton to retain the most recent entry from a user and delete all previous entries.
My master sheet is set out as such...
A, B, C, D,
UserID, Date, Time, Response
The users could potentially submit multiple entries on the same day. Ideally I would like to be able to click a CommandButton and delete each user's submission but retain their most recent one (based on "UserID", then "Date", then "Time").
I've searched all day for a solution and I've come close but I can not figure out a code that accounts for my three variables ("UserID", then "Date", then "Time").
View 5 Replies
View Related
Feb 20, 2014
I need a formula that will calculate the percentage of the total duration between two dates (in dd.mm.yyyy hh:mm format) that a third, intervening date represents.
As an example:
a) 10th Jan 2014 09:00
b) 20th Jan 2014 09:00
c) 30th Jan 2014 09:00
In this case the answer sould be 50% because 'b' represents exactly half of the time elapsed between 'a' and 'c'.
Note: the formula needs to work effectively across the boundary between two consecutive years.
View 1 Replies
View Related
Jul 13, 2009
The col B is a list and col A is a list of Col B + some other. My requirement is to find col C which does not contain the terms that are not mentioned in col B. ie: A-B to give C. I am attaching sheet for your use.
View 2 Replies
View Related
Oct 18, 2007
I have the following formula.
=SUMIF(hfx!A:A,"123456789",hfx!J:J)
very simple.
for the totals in J, if there is a (-) negative number, i would like that excluided from the equation.
View 9 Replies
View Related
Jun 22, 2014
I have two Columns
AC Tm%T%
DOMEQ25%26.5000%
INTEQ17%18.0200%
DOMPR10%10.6000%
INTPR 0%0.0000%
ALTIN 0%0.0000%
DOMFI29%30.7400%
INTFI 6%0.0000%
DOMFR10%10.6000%
CASH 3%3.1800%
Total 100.00%99.64%
The Tm column is the original model percentages for each class. Class INTFI is to be excluded (6%) so I need to adjust each of the other % accordingly and still add up to 100. I thought you simply increase all other % by the excluded amount but I end up with 99.64.
The equation I am using fot T is =C15+($C$21*C15) where C21 is the 6% cell in Tm.
View 1 Replies
View Related
Jun 26, 2013
Can a single(or multiple) column be shown on sheet but not printed? I have a list with a bunch of vlookups in column C-H, a numbered column in A, the code used for the vlookup in B. I do not want to print the code(column B) but do want to print A,C-H. I do need to see the column B in sheet. How do I do this?
View 2 Replies
View Related
Mar 29, 2014
How to select all sheets except the 4 sheets to be excluded using macro. i want to select worksheet with a macro except sheet name
"sheet1"~"sheet4",
View 1 Replies
View Related
Jan 15, 2009
Today I am having a very annoying problem that really has me stumped – I need to work out the lag between a Due Date and Delivered Date
But as people sometimes manage to deliver on the Due Date it needs to show a zero (as in they got it in on time) but using the formula below the result is a 1 and I want a zero
Can anyone help me please? I have tried putting assorted -1s in to the formula and it looks like it might work until I copy down and find that if a person delivered one day early the result shows -3 for example!
View 7 Replies
View Related
May 3, 2009
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.
View 5 Replies
View Related
May 14, 2008
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): ...
View 4 Replies
View Related
