cell formula to compare dates and select second gratest date in a series

E

exeluser1

New Member
Joined
Jun 18, 2017
Messages
6
Hi all!
I have a table with Commercial Conditions and start and end dates.
Sometimes, new condition begin before the end date of previous condition, and I need to be able to find it and correct an end date of a previous condition. (the end date is the start date of next row of same condition minus 1 day)
Date format here mm-dd-yyyy:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]row index[/TD]
[TD]ID[/TD]
[TD]Start Date[/TD]
[TD]End Date[/TD]
[TD]Actual End date (to be calculated by formula)[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]ID1[/TD]
[TD]01-01-2014[/TD]
[TD]10-20-2049[/TD]
[TD]03-19-2014 (03-20-2014 minus 1 day)[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]ID2[/TD]
[TD]04-01-2015[/TD]
[TD]01-01-2020[/TD]
[TD]01-01-2020[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]ID3[/TD]
[TD]06-01-2014[/TD]
[TD]07-01-2014[/TD]
[TD]07-01-2014[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]ID1[/TD]
[TD]03-20-2014[/TD]
[TD]10-10-2015[/TD]
[TD]04-30-2014 (05-01-2014 minus 1 day)[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]ID1[/TD]
[TD]05-01-2014[/TD]
[TD]01-01-2019[/TD]
[TD]01-01-2019[/TD]
[/TR]
</tbody>[/TABLE]

So, in that case, a formula in right column row 1 shall find all rows with same ID (row 1, 4, 5), check that end date of current row (10-20-2049) is greater than start date of next closest start date (03-20-2014), and select that date minus 1 day as a new end date (03-19-2014).

Similar in row 4: the formula shall find all same IDs (row 1, 4, 5), check that end date of current row (10-10-2015) is greater than start date of next closest start date (05-01-2014), and select that date minus 1 day as a new end date (04-30-2014).

In row 5: the formula shall find all same IDs (row 1, 4, 5), check that there are no next closest start dates, and return the end date of row 5.

Please help me to do that.

Thanks!
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
Sort by date Sort by votes
[TABLE="width: 670"]
<colgroup><col><col span="2"><col><col span="6"></colgroup><tbody>[TR]
[TD]id[/TD]
[TD]startdate[/TD]
[TD]enddate[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]01/01/2017[/TD]
[TD="align: right"]01/04/2017[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]08/01/2017[/TD]
[TD="align: right"]10/04/2017[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]15/01/2017[/TD]
[TD="align: right"]19/04/2017[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]02/03/2017[/TD]
[TD="align: right"]28/04/2017[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]05/04/2017[/TD]
[TD="align: right"]07/05/2017[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]05/02/2017[/TD]
[TD="align: right"]16/05/2017[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 3"]sort by id then startdate[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]id[/TD]
[TD]startdate[/TD]
[TD]enddate[/TD]
[TD]newenddate[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]01/01/2017[/TD]
[TD="align: right"]01/04/2017[/TD]
[TD="align: right"]28/04/2017[/TD]
[TD]#####[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]02/03/2017[/TD]
[TD="align: right"]28/04/2017[/TD]
[TD="align: right"]07/05/2017[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]05/04/2017[/TD]
[TD="align: right"]07/05/2017[/TD]
[TD="align: right"]10/04/2017[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]08/01/2017[/TD]
[TD="align: right"]10/04/2017[/TD]
[TD="align: right"]19/04/2017[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]15/01/2017[/TD]
[TD="align: right"]19/04/2017[/TD]
[TD="align: right"]16/05/2017[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]05/02/2017[/TD]
[TD="align: right"]16/05/2017[/TD]
[TD="align: right"]00/01/1900[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]#####[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="colspan: 6"]=IF(A13=A12,IF(B13<C12,IF(C13>C12,C13,C12),C13),C13)[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
[TABLE="width: 670"]
<colgroup><col><col span="2"><col><col span="6"></colgroup><tbody>[TR]
[TD]id[/TD]
[TD]startdate[/TD]
[TD]enddate[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]01/01/2017[/TD]
[TD="align: right"]01/04/2017[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]08/01/2017[/TD]
[TD="align: right"]10/04/2017[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]15/01/2017[/TD]
[TD="align: right"]19/04/2017[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]02/03/2017[/TD]
[TD="align: right"]28/04/2017[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]05/04/2017[/TD]
[TD="align: right"]07/05/2017[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]05/02/2017[/TD]
[TD="align: right"]16/05/2017[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 3"]sort by id then startdate[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]id[/TD]
[TD]startdate[/TD]
[TD]enddate[/TD]
[TD]newenddate[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]01/01/2017[/TD]
[TD="align: right"]01/04/2017[/TD]
[TD="align: right"]27/04/2017[/TD]
[TD]#####[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]02/03/2017[/TD]
[TD="align: right"]28/04/2017[/TD]
[TD="align: right"]06/05/2017[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]05/04/2017[/TD]
[TD="align: right"]07/05/2017[/TD]
[TD="align: right"]07/05/2017[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]08/01/2017[/TD]
[TD="align: right"]10/04/2017[/TD]
[TD="align: right"]10/04/2017[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]15/01/2017[/TD]
[TD="align: right"]19/04/2017[/TD]
[TD="align: right"]15/05/2017[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]05/02/2017[/TD]
[TD="align: right"]16/05/2017[/TD]
[TD="align: right"]16/05/2017[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]#####[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="colspan: 6"]=IF(A13=A12,IF(B13<C12,IF(C13>C12,C13-1,C12),C12),C12)[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
This seems to give you the answers you showed...
=IFERROR(IF(D2>INDEX(D3:$D$7,MATCH(B2,B3:$B$7,0)),INDEX(C3:$C$7,MATCH(B2,B3:$B$7,0))-1,D2),D2)
copied down

Adjust the lower range as needed
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question Question
Help with Sumifs formula involving dates
Replies
5
Views
85
Joe4
Joe4
L
  • Solved
Help Please
2 3
Replies
21
Views
532
Asbestos_Jen
Asbestos_Jen
S
  • Question Question
List all staff names that worked under a manager on a certain date
Replies
5
Views
403
Fluff
Fluff
Jyggalag
  • Solved
Formula to calculate between dates
Replies
4
Views
707
Peter_SSs
Peter_SSs
D
  • Question Question
Formula help needed for working with days and times.
Replies
0
Views
423
dshtesy
D

Forum statistics

Threads
1,224,825
Messages
6,181,189
Members
453,020
Latest member
Mohamed Magdi Tawfiq Emam

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top