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

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
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

L
  • Solved
Help Please
2 3
Replies
21
Views
489
Asbestos_Jen
Asbestos_Jen
S
  • Question Question
List all staff names that worked under a manager on a certain date
Replies
5
Views
340
Fluff
Fluff
Jyggalag
  • Solved
Formula to calculate between dates
Replies
4
Views
647
Peter_SSs
Peter_SSs
D
  • Question Question
Formula help needed for working with days and times.
Replies
0
Views
319
dshtesy
D
S
  • Question Question
Returning 1 or 0 based on multiple dates
Replies
2
Views
342
bebo021999
B

Forum statistics

Threads
1,223,911
Messages
6,175,325
Members
452,635
Latest member
laura12345

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