Find the first date after, a specified date

[Shodi]

Hi, this is teh first time I am posting on this site. I have been here before have has found help in figuring out complex calculations. So I am here again, this time as a member, requesting help to figure out a formula that has me baffled.

I have a whole spreadsheet full of data like that given below.
It shows the date someone was called in column B and the date the person attended in Column C.
As you can see the "Call Date" for all unique "Names" are identical where as the "Attended Date" varies.
What I need is to extract in a different sheet the "Attended Date" that is immediately after the "Call Date" for each unique individual.
E.g
A2= Fred, B2= 17-Jan-16, C2= 29-Jan-16 (the 1st date in "Attended Date" this is common for Fred, after 17-Jan-1)
A3= Hank, B3= 7-Feb-16, C3= 4-Mar-16 (the 1st date in "Attended Date" this is common for Hank, after 7-Feb-16)
A4= John, B4= 25-Feb-16, C4= 1-Mar-16 (the 1st date in "Attended Date" this is common for John, after 25-Feb-16)

and so on.

Please help, am on the clock.

Name	Call Date	Attended Date
Fred	17-Jan-16	11-Jan-16
Fred	17-Jan-16	29-Jan-16
Fred	17-Jan-16	13-Mar-16
Fred	17-Jan-16	14-Mar-16
Fred	17-Jan-16	14-Mar-16
Hank	7-Feb-16	1-Jan-16
Hank	7-Feb-16	4-Mar-16
Hank	7-Feb-16	22-Mar-16
John	25-Feb-16	2-Jan-16
John	25-Feb-16	9-Feb-16
John	25-Feb-16	1-Mar-16
John	25-Feb-16	6-Mar-16

<tbody>
</tbody>

 

[Fishboy]

Hi, this is teh first time I am posting on this site. I have been here before have has found help in figuring out complex calculations. So I am here again, this time as a member, requesting help to figure out a formula that has me baffled.

I have a whole spreadsheet full of data like that given below.
It shows the date someone was called in column B and the date the person attended in Column C.
As you can see the "Call Date" for all unique "Names" are identical where as the "Attended Date" varies.
What I need is to extract in a different sheet the "Attended Date" that is immediately after the "Call Date" for each unique individual.
E.g
A2= Fred, B2= 17-Jan-16, C2= 29-Jan-16 (the 1st date in "Attended Date" this is common for Fred, after 17-Jan-1)
A3= Hank, B3= 7-Feb-16, C3= 4-Mar-16 (the 1st date in "Attended Date" this is common for Hank, after 7-Feb-16)
A4= John, B4= 25-Feb-16, C4= 1-Mar-16 (the 1st date in "Attended Date" this is common for John, after 25-Feb-16)

and so on.

Please help, am on the clock.

Name	Call Date	Attended Date
Fred	17-Jan-16	11-Jan-16
Fred	17-Jan-16	29-Jan-16
Fred	17-Jan-16	13-Mar-16
Fred	17-Jan-16	14-Mar-16
Fred	17-Jan-16	14-Mar-16
Hank	7-Feb-16	1-Jan-16
Hank	7-Feb-16	4-Mar-16
Hank	7-Feb-16	22-Mar-16
John	25-Feb-16	2-Jan-16
John	25-Feb-16	9-Feb-16
John	25-Feb-16	1-Mar-16
John	25-Feb-16	6-Mar-16

<tbody>
</tbody>

Hi Shodi, welcome to the boards.

The following array formula will give you the answer for Fred. An array formula needs to be entered with CTRL+SHIFT+ENTER not just ENTER or it will return a #Value! error.

Basically this formula is looking for the minimum date in column C where the name in column A is Fred and the date in column C is greater than the call date in B2. That should hopefully give you enough of an idea to work out how to find out the dates for Hank and John

=MIN(IF(A2:A13="Fred",IF(C2:C13>B2,C2:C13)))

 

[Shodi]

Dear Fishboy,

Thanks a lot. It is working for Fred. I'll try get it to work for the other too. Thank a lot.

 

[Shodi]

Not working...help please

 

[Fishboy]

Not working...help please

Hank:

=MIN(IF(A2:A13="Hank",IF(C2:C13>B7,C2:C13)))


John:

=MIN(IF(A2:A13="John",IF(C2:C13>B10,C2:C13)))

 

[Shodi]

Dear Fishboy,

This will require me to manually change the requirements for each unique name. There are over 100,000 unique names.

What I need is a formula that does the below given logic.

Look for the value in C (Attended date), in the range of values in C that has a common value in A(name), that is the next greatest value to the common value in B (Call Date).

 

[Fishboy]

Dear Fishboy,

This will require me to manually change the requirements for each unique name. There are over 100,000 unique names.

What I need is a formula that does the below given logic.

Look for the value in C (Attended date), in the range of values in C that has a common value in A(name), that is the next greatest value to the common value in B (Call Date).

Hmm, that is another matter entirely and significantly more complicated than your original post implied. It will require further testing at this end and I cannot guarantee I will have a solution for that.

Where are you expecting these results to be returned? I think the best place will be perhaps column D of each line as the cells can at least be referenced to get the names etc without the need to manually update every time.

 

[Jonmo1]

If you create a list of the unique names in say D2:D100, then you do
=MIN(IF(A$2:A$13=D2,IF(C$2:C$13>B7,C$2:C$13)))
And fill it down.

Creating the unique list is easy with the Advanced Filter - Unique items.