Find next date in a column

[ms500716]

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

 

[MrKowz]

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]

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]

Does this work for you in D2 copied down?

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

 

[ms500716]

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]

Did you try my formula?

 

[T. Valko]

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]

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

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