Find next date in a column

ms500716

New Member
Joined
May 6, 2004
Messages
47
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
 

Some videos you may like

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

MrKowz

Well-known Member
Joined
Jun 30, 2008
Messages
6,653
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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
Joined
May 9, 2009
Messages
16,623
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),"")
 

ms500716

New Member
Joined
May 6, 2004
Messages
47

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
 

T. Valko

Well-known Member
Joined
May 9, 2009
Messages
16,623
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
Joined
May 6, 2004
Messages
47
Yes Andrew, I did try your formula and it worked.

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

Watch MrExcel Video

Forum statistics

Threads
1,123,384
Messages
5,601,323
Members
414,442
Latest member
Oleg

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
Top