Pick The Earliest Date From A Selection Of Dates

Jan 24, 2007

There is a speadsheet at work with a column which has a range of dates (from 18/05/2003 - date) which information gets drawn from every Friday. The information needed from the column is pretty simple but lengthy to expalin. For example...

[url]

As you can the dates are in no order...and need to stay that way. The information needed every week is shown here...

[url]

I have got the formula for the number of enteries between each date but strugling with the earliest date within the dates. I can do it manually by auto filtering the column and looking for the date closest to the earliest date but this is long.

The dates change on a weekly basis...just to throw a spanner in the works.

View 9 Replies


ADVERTISEMENT

Pick Earliest Date For A Record From Row Of Those Records?

May 30, 2014

We have multiple work orders per opportunity, and each work order has a date. I have a sheet of these work orders which shows the work order #, work order date, and opportunity date. What I want to do is add a column which reports the work order date if it is the earliest instance of a work order for the opportunity (said differently, the first work order for the opportunity). Is there a way to write a formula to report whether the date of the row is the earliest date for a given opportunity, all in the same table?

View 1 Replies View Related

Earliest Date From A Column Of Dates

Dec 10, 2008

I have a column of dates listed at the bottom of this post. I am looking to have a formula in a cell that gives me the earliest date from the column. I have tried Min() and Small() with no success. Is it perhaps the "AM" and "PM" that is throwing things off? A small example of the data:

11/21/2008 AM11/09/2008 AM11/09/2008 PM11/09/2008 PM11/10/2008 PM11/11/2008 AM11/11/2008 AM
and it should return 11/09/2008. Thanks in advance any and all.
Ed

View 9 Replies View Related

Lookup For Succeeding Dates After Earliest Date

Dec 8, 2013

On 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.

View 3 Replies View Related

Finding Earliest And Latest Dates

Jan 4, 2009

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

View 9 Replies View Related

Pick Range Of Dates And Find Number Of Days Without Sales Between Those Dates

Feb 23, 2014

Here is my set up:

A2 to BF2 is a range of dates

A3 to BF3 are sales. Days without sales are 0.00

I want to pick a range of dates and find the number of days without sales between those dates. So, a formula that will look to a start date in A1 and an end date in B2, and then count the number of days that did not have sales between. Index/Match/Countif/Dateif I can't seem to make anything work.

View 3 Replies View Related

Pull Out Earliest And Latest Dates Associated With Text String In Workbook

Feb 7, 2014

I'm trying to pull out the earliest and latest dates associated with a text string in a workbook.

Min Max dates example.xlsx

i.e. in this example, I want one column to display the earliest date associated with DU 145, and the next column to display the latest.

From what I understand, I need an array formula to do this and have written the following, which works:

=MIN(IF($A$2:$A$6=A2,$B$2:$B$6))
(confirmed with CSE) - and the same for MAX.

Where I'm struggling is that DU 145 may be entered in the workbook as DU145, DU 145 or DU-145 and I need to take all of them into account. I tried:

=MIN(IF($A$2:$A$6="du*145",$B$2:$B$6))

As this works in a COUNTIF formula in the same sheet. But this just returns a date of 00/01/1900. I'm new to array formulae & haven't really worked them out yet. Is there a way to do this? I assume it's something to do with the way I'm entering text as removing the wildcard * makes no difference.

View 4 Replies View Related

Multi Selection Pick List

Aug 19, 2009

My organization whats to provide other a excell spreadsheet ( see sample attached) with pick list for certain cells. We have successfull completed adding the pick list. HOWEVER, at times we would like the end user to be able to pick multiple choices form the the pick list

In the example - in column c the user can pick from the drop down list
A
B
V
or
W

However we want the end user to be able to select any combination, single choice, multiple choice ect

In this example with the above pick list created


How can the end use pick A and W or A, V and W etc

View 10 Replies View Related

Find Out The Earliest Date

Feb 25, 2009

I have two columns in a table; Plan and Date.

Plan Date
A 01/02/03
B 12/08/05
C 02/04/06
C 06/04/07
C 06/04/07

Now I want the third column, also a date, so that it returns the earliest date for the repeating plan type.

Plan Date New Date
A 01/02/03 01/02/03
B 12/08/05 12/08/05
C 02/04/06 02/04/06
C 06/04/07 02/04/06
C 06/04/07 02/04/06

View 14 Replies View Related

Select The Earliest Date

Mar 14, 2008

a formula to pick a first and last date from two lists of dates.

I have attached a spreadsheet to show the exact problem.

View 14 Replies View Related

How To Remove Duplicates With Earliest Date

Feb 14, 2014

remove duplicate.PNG

is there a way to remove the duplicates in column ICAP ID... but only remove the icap id that has the earliest completed date(completion_dt).

I attached an example excell...

View 2 Replies View Related

Show Next Date In Sequence From Earliest

Apr 11, 2009

i have 6 columns representing people B6:B500 C D E F G all the same in each row a date as worked will be entered for each person say from b6 to g6 2,6,1,3,5,4
in A1 i want to show the earliest date worked which is 1 then say 7 is entered into d7 i need A1 to show 2 as the next earliest and the same to happen as each date is entered i am using 2007 at work and 2003 at home.

View 4 Replies View Related

Finding Earliest Date And Transaction By Name

Apr 2, 2012

I'm working through a large database on sheet 1 that has multiple transactions for each person, what i am trying to do is find the earliest date and the first amount that each person deposited money. The findings will go into a table on sheet 2

This is a dummy data of what i have: Sheet 1 - database....

View 5 Replies View Related

How To Find Earliest Date On Row Across Six Columns

Apr 5, 2013

I'm trying to find a formula that will return the earliest date in a cell looking at the six columns to the left of the earliest date column.

See below for example.

Employee Date1 Date2 Date3 Date4 Date5 Date6 EARLIEST DATE

Long Johns 7/1/2012 7/1/2012 7/1/2012 7/1/2012 7/1/2010 7/1/2012 7/1/2010

View 2 Replies View Related

Return Corresponding Data From Earliest Date

Jun 1, 2007

What I understand is that balls are drawn in each dated lotto draw and the numbers of the balls thus drawn are entered into a spreadsheet against the actual date of the drawing - and so forth and so forth for each successive drawing.

Here is my version of the question:

If I want to look up the LAST date that a specific ball was drawn, the array formula that daddylonglegs provided works 100%, but when I replace the MAX in that formula with MIN (to get the earliest date??), it ALWAYS responds with 00/01/1900 with the cell formatted as a date, or simply 0 if it is formatted as a number.

I made sure that the ranges did not include empty cells and still it gives me that answer.

I attach the same workbook with daddylonglegs' formula in column D and my "tries" in column F.

View 9 Replies View Related

Returning Earliest And Latest Date For Unique ID?

Jun 25, 2014

I need an excel function to return the earliest and latest date for a particular/unique ID.

For example: In my sample workbook, the earliest date for all the columns with ID = 1 should return 11/1/2001 and the latest date should be 12/2/2011. If all the dates for a particular ID are the same, it should just return that date. For example, for ID = 2, all dates are 5/5/2010 so the earliest and latest dates should equal 5/5/2010. The earliest and latest dates have to be for each unique ID.

View 5 Replies View Related

Find Earliest Date Based On Certain Criteria

Jan 25, 2010

Ok so let me set this up. I have 2 columns: .....

View 14 Replies View Related

Array Formula - The Earliest Date After Today

Jan 16, 2008

I tried searching through the forums, but I don't exactly know how to word my question!

I have a workbook with two sheets: Meetings and MasterStatus

On the both sheets I have taskID for a specific task.

On the MasterStatus sheet, I want to use an array to look up the next meeting date for each taskID (Column C), referencing the Meetings sheet (Column E) to do so.

The formula I have so far doesn't work:

=MIN(
IF(
AND(
Meetings!$A$2:$A$3000>TODAY()-1,
NOT(ISBLANK(Meetings!$A$2:$A$3000)),
Meetings!$E$2:$E$3000=$C$7),
Meetings!$A$2:$A$3000,
0
)
)

View 9 Replies View Related

Return Earliest Date Without Corresponding Blank Cell

Apr 26, 2008

I have a date range, containing blanks and values. I need a function to show the earliest date that contains a non-blank value. The function should be robust enough that I can sort by date and still show the correct data.

Example...(correct answer would be 10/26/2006)

Date ... Value
10/23/2006
10/24/2006
10/25/2006 0.08
10/26/2006 0.10
10/27/2006
10/28/2006
10/29/2006 0.18
10/30/2006 0.20
10/31/2006
11/01/2006
11/02/2006
11/03/2006 (0.18)
11/04/2006 (0.15)

View 5 Replies View Related

Min If Function Doesn't Work For Earliest Date Lookup

May 23, 2014

I am having issues with a min if formula, even though i enter it as an array (ctrl +shift+enter), it only returns a zero.

The formula is supposed to return the earliest date from Raw data if the name of the company matches the one from the data validation ('Company lookup'!D3).

Dates are in column D and company name in column M

I checked, the dates are all formatted as dates

=MIN(IF(RAW!$M:$M,'Company lookup'!D3,RAW!$D:$D))

View 3 Replies View Related

Loop A Comparison And On Match Return Earliest Date?

May 25, 2012

Using online examples I am usually capable to reconstruct whatever I need. However, it's the combination of things I need to do now, which has left me banging my head on the keyboard for days now

Here's the data I am working with:

Sheet 1:

Sheet 2:

And here's what I need to get done:

From sheet 1, select first ID entry from Column A.Find matching ID in Column A of sheet 2On match, find in Column B the earliest date belonging to the concerned IDCopy that earliest date next to the corressponding ID in Column B back on sheet 1Return to step 1 and repeat for next ID entry. Do until end of list (sheet1)

So the result should look as following on sheet 1:

The major issue I am having is the combination of step 3 and 5. Because it probably means something like moving through an array that's within an array through which one is moving. And I am just missing that bit of experience that allows me to make that thinking step. I just keep falling off if you know what I mean...

View 3 Replies View Related

Macro To Run Autofilter To Pick Out Data Between Two Dates

Apr 30, 2014

I am having trouble with the following macro,

[Code] .....

When I press F8 and step through the macro, the StartDate and EndDate both seem to populate correctly.

The macro seems to go through the motions but returns NO results, unlike the recorded macro.

Macro code to "Clear Filter".

View 2 Replies View Related

Putting Index Column - VLookup Does Not Pick Up Dates?

Apr 5, 2014

does it not like dates? i have columns of data and i wish to search it for each month and then print a months worth. here is part of the data. maybe vlookup does not like dates? i tried putting an index column on the left, 1,2,3,4... but it still would not pick up "Jun"

View 8 Replies View Related

Date/Time Formula: Pick Up A Date With Time Entry On A Worksheet And Place It Into A TextBox On A UserForm

Jun 17, 2006

I am attempting to pick up a date with time entry on a worksheet and place it into a TextBox on a UserForm. Format on the sheet is mm/dd/yyyy h:mm AM/PM. The UserForm is placing the value as mm/dd/yyyy 12:00 AM. here is the

Private Sub UserForm_Initialize()
If Not Range("dDate").Value = "" Then
TextBox2.Value = Range("dDate").Value
TextBox2.Text = Format(DateValue(TextBox2.Text), "mm/dd/yy h:mm AM/PM")
Else
TextBox2.Value = ""
TextBox2.SetFocus
End If
End Sub


"dDate" is the named range where the date is sitting. The format is also set on the TextBox2 exit event. Can anyone see why only the date portion is being transfered with the default 12:00 AM for no time component of the value?

View 3 Replies View Related

Pick Pre-determined Date Entry In Userform?

Aug 11, 2014

I am trying to get my User Form to work whereby I choose a pre-determined date entry. I have tried some coding but cannot get it to work. What code I need to add in VBA for this to work? I have tried to alter the time entry from my previous post that was solved but are doing something wrong.

View 11 Replies View Related

Calendar Popup For Users To Pick A Date

Jan 10, 2007

Is it possible to have a cell "pop up" a calendar for users to pick a date from and have that date entered into the cell?

View 9 Replies View Related

Macro To Pick Up A Range Of Date To Filter On In A Pivot Table

Jun 25, 2008

When using a Pivot Table I regularly filter on known data specific to me i.e. WBS codes.

Column A contains the WBS Codes which can be as many a 1000, however, I only need to filter out the 10 or so I require, and currently I uncheck all the records and manually scroll through the list to check the ones I require.

In order to speed things up I recorded a macro which works fine, however, If I need to check any new codes I would have to record the macro again, as I tried to edit the Macro and all it does is to un check all the other codes I don't need.

Is it possible to create a Macro that picks up a range of data that is then used to filter on. (i.e. only the records I require)

View 9 Replies View Related

Changing Dates Based On Dropdown Selection?

Feb 12, 2014

I am trying to develop a worksheet that will adjust dates based on a selection from a drop down box. I can do basic formulas in excel

I am trying to get the drop down box in "Revisit Type" to include three selections ("Sanitation", "Spark", and "High Five"). Once the dropdown box is created I want to be able to make a selection and enter an initial visit date under "Initial Visit Date" then have the "Earliest Return Visit Date" and the "Latest Return Visit Date" automatically populated based on this information below:

Sanitation or Spark = 10-30 day window
High Five = 10-21 day window

I am at a loss . I have attached a sample of the workbook for reference.

View 3 Replies View Related

Convert Text To Dates - Cannot Group This Selection

Jun 26, 2014

I am having an issue with converting what seems like Text to Dates. I am trying to group dates in my pivot table, but I keep getting the error "cannot group this selection". My dates are left justified, causing me to believe that they are text.

When I format the cells, however, the cells say that they are already in date format. When I try using Text to Columns in the Data Tab and selecting MDY, I get the error "You cannot move a pivot table table report or insert worksheet cells, rows, or columns."

Not sure what to do from here, as I would like to group dates by weeks.

View 9 Replies View Related

Difficulties With Dates (convert The Column Of Dates To Say Mar 14 2009 Type Date)

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







Copyrights 2005-15 www.BigResource.com, All rights reserved