# Find next date in a column

#### ms500716

##### New Member
I have a travel claim, with a column for beginning date, and a column for end date. There can be multiple trips entered on the sheet, with multiple beginning dates and end dates.

My end date is not always on the same row as my beginning date, and I need to be able to calclate the number of days between the beginning date and end date. This would work beautifully if there was only one trip per sheet, but there's not. Can someone help me with a formula to find the next date in the end date column?

In this example, there are 2 trips. The first trip spans 3 days and the second spans 2 days. I need to automatically calculate the "Days", but the number of rows for each trip can vary.

Trip Beginning Date End Date Days

NY to DC 3/1/2011
DC to Dallas
Dallas to OKC
OKC to NY 3/4/2011 3
NY to Miami 3/5/2011
Miami to LA
LA to Chicago 3/7/2011 2

### Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},\$Z\$1:\$Z\$99,\$Y\$1:\$Y\$99),2,False) to lookup Y values to left of Z values.

#### MrKowz

##### Well-known Member
Try the following - NOTE that the formula starts in C2, not C1.

Excel 2003<TABLE style="BORDER-RIGHT: #a6aab6 1px solid; BORDER-TOP: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BORDER-BOTTOM: #a6aab6 1px solid; BORDER-COLLAPSE: collapse; BACKGROUND-COLOR: #ffffff" cellPadding=2 rules=all><COLGROUP><COL style="BACKGROUND-COLOR: #e0e0f0" width=25><COL><COL><COL></COLGROUP><THEAD><TR style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0; TEXT-ALIGN: center"><TH></TH><TH>A</TH><TH>B</TH><TH>C</TH></TR></THEAD><TBODY><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">1</TD><TD>NY to DC</TD><TD style="TEXT-ALIGN: right">3/1/2011</TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">2</TD><TD>DC to Dallas</TD><TD style="TEXT-ALIGN: right"></TD><TD></TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">3</TD><TD>Dallas to OKC</TD><TD style="TEXT-ALIGN: right"></TD><TD></TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">4</TD><TD>OKC to NY</TD><TD style="TEXT-ALIGN: right">3/4/2011</TD><TD style="TEXT-ALIGN: right">3</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">5</TD><TD>NY to Miami</TD><TD style="TEXT-ALIGN: right">3/5/2011</TD><TD></TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">6</TD><TD>Miami to LA</TD><TD style="TEXT-ALIGN: right"></TD><TD></TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">7</TD><TD>LA to Chicago</TD><TD style="TEXT-ALIGN: right">3/7/2011</TD><TD style="TEXT-ALIGN: right">2</TD></TR></TBODY></TABLE><TABLE style="BORDER-RIGHT: #a6aab6 1px solid; BORDER-TOP: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BORDER-BOTTOM: #a6aab6 1px solid; BORDER-COLLAPSE: collapse; BACKGROUND-COLOR: #ffffff" cellPadding=2 rules=all><THEAD><TR style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0; TEXT-ALIGN: center"><TH>Sheet2</TH></TR></TD></THEAD><TBODY></TBODY></TABLE>

<TABLE style="BORDER-RIGHT: black 2px solid; PADDING-RIGHT: 0.4em; BORDER-TOP: black 2px solid; PADDING-LEFT: 0.4em; PADDING-BOTTOM: 0.4em; BORDER-LEFT: black 2px solid; PADDING-TOP: 0.4em; BORDER-BOTTOM: black 2px solid; BORDER-COLLAPSE: collapse; BACKGROUND-COLOR: #ffffff" cellPadding=2 rules=all width="85%"><TBODY><TR><TD style="PADDING-RIGHT: 6px; PADDING-LEFT: 6px; PADDING-BOTTOM: 6px; PADDING-TOP: 6px">Worksheet Formulas<TABLE style="BORDER-RIGHT: #a6aab6 1px solid; BORDER-TOP: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BORDER-BOTTOM: #a6aab6 1px solid; BORDER-COLLAPSE: collapse; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: center" cellPadding=2 rules=all width="100%"><THEAD><TR style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0"><TH width=10>Cell</TH><TH style="PADDING-LEFT: 5px; TEXT-ALIGN: left">Formula</TH></TR></THEAD><TBODY><TR><TH style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0" width=10>C2</TH><TD style="TEXT-ALIGN: left">=IF(AND(B2<>"",B1=""),B2-LOOKUP(9.99E+307,\$B\$1:B1),"")</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

#### T. Valko

##### Well-known Member
I have a travel claim, with a column for beginning date, and a column for end date. There can be multiple trips entered on the sheet, with multiple beginning dates and end dates.

My end date is not always on the same row as my beginning date, and I need to be able to calclate the number of days between the beginning date and end date. This would work beautifully if there was only one trip per sheet, but there's not. Can someone help me with a formula to find the next date in the end date column?

In this example, there are 2 trips. The first trip spans 3 days and the second spans 2 days. I need to automatically calculate the "Days", but the number of rows for each trip can vary.

Trip Beginning Date End Date Days

NY to DC 3/1/2011
DC to Dallas
Dallas to OKC
OKC to NY 3/4/2011 3
NY to Miami 3/5/2011
Miami to LA
LA to Chicago 3/7/2011 2
Maybe this...

Assuming the dates are in column B starting in cell B2.

Enter this formula in cell C3 and copy down as needed...

=IF(MOD(COUNT(B\$2:B3),2)=0,B3-LOOKUP(1E100,B\$2:B2),"")

#### Andrew Poulsom

##### MrExcel MVP
Does this work for you in D2 copied down?

=IF(C2,C2-MAX(B\$1:B2),"")

#### ms500716

##### New Member

ADVERTISEMENT

Sorry guys, I don't know how to add an excel grid to the post. The beginning date is in column B and the ending date is in column C. My formula will go in column D

#### Andrew Poulsom

##### MrExcel MVP
Did you try my formula?

#### T. Valko

##### Well-known Member
Sorry guys, I don't know how to add an excel grid to the post. The beginning date is in column B and the ending date is in column C. My formula will go in column D
Are the dates always in ascending order?

#### ms500716

##### New Member
Yes Andrew, I did try your formula and it worked.

The dates will be in ascending order, both the beginning and the end dates.

### Similar threads

Replies
6
Views
304
Replies
1
Views
132
Replies
6
Views
278
Replies
1
Views
143
Replies
23
Views
743

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Threads
1,163,851
Messages
5,833,971
Members
430,249
Latest member
Muka

### 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

### 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