Assigning day number to travel data

[mtnbaik]

I am looking for assistance with a terribly formatted data set I was given with travel data. I need to assign number to the legs (days) of each trip, but am stumped on a formula/function that can help me. I've attached a sample set where I've manually entered the day numbers in the screenshot below to show you what I'm looking to do. Basically, what i am looking to do is assign 1 to the first day of trip, 2 to the second day of trip, 3 to third day, and so on. So if there was 3 parts to the day 1 travel, I need it to display all three as day 1. Thank you for any help!

Book1
	A	B
1	Day	Segment Departure Date/Time
2	1	Oct 3, 2016 9:06:00 AM
3	2	Oct 8, 2016 12:55:00 PM
4		Segment Departure Date/Time
5	1	Oct 3, 2016 9:06:00 AM
6	2	Oct 8, 2016 12:55:00 PM
7		Segment Departure Date/Time
8	1	Oct 16, 2016 9:23:00 AM
9	2	Oct 21, 2016 10:17:00 AM
10		Segment Departure Date/Time
11	1	Nov 7, 2016 8:55:00 AM
12	1	Nov 7, 2016 1:30:00 PM
13	1	Nov 7, 2016 1:40:00 PM
14	2	Nov 11, 2016 3:00:00 PM
15	2	Nov 11, 2016 6:26:00 PM
16		Segment Departure Date/Time
17	1	Nov 7, 2016 8:20:00 AM
18	1	Nov 7, 2016 8:25:00 AM
19	2	Nov 11, 2016 11:40:00 AM
20	2	Nov 11, 2016 12:09:00 PM
21		Segment Departure Date/Time
22		Nov 7, 2016 12:15:00 PM
23		Nov 11, 2016 12:00:00 PM
24		Segment Departure Date/Time
25		Nov 7, 2016 7:00:00 AM
26		Nov 11, 2016 12:40:00 PM
27		Nov 11, 2016 12:46:00 PM
28		Nov 11, 2016 4:15:00 PM
29		Segment Departure Date/Time
30		Oct 6, 2016 12:12:00 PM
31		Oct 6, 2016 2:54:00 PM
32		Oct 9, 2016 2:11:00 PM
33		Oct 9, 2016 7:40:00 PM
Sheet1

Cell Formulas
Range	Formula
A1	Day
A2	1
A3	2
A5	1
A6	2
A8	1
A9	2
A11	1
A12	1
A13	1
A14	2
A15	2
A17	1
A18	1
A19	2
A20	2
B1	Segment Departure Date/Time
B2	42646.37916666
B3	42651.53819444
B4	Segment Departure Date/Time
B5	42646.37916666
B6	42651.53819444
B7	Segment Departure Date/Time
B8	42659.39097222
B9	42664.42847222
B10	Segment Departure Date/Time
B11	42681.37152777
B12	42681.5625
B13	42681.56944444
B14	42685.625
B15	42685.76805555
B16	Segment Departure Date/Time
B17	42681.34722222
B18	42681.35069444
B19	42685.48611111
B20	42685.50625
B21	Segment Departure Date/Time
B22	42681.51041666
B23	42685.5
B24	Segment Departure Date/Time
B25	42681.29166666
B26	42685.52777777
B27	42685.53194444
B28	42685.67708333
B29	Segment Departure Date/Time
B30	42649.50833333
B31	42649.62083333
B32	42652.59097222
B33	42652.81944444

 

[Gerald Higgins]

Re: Help assigning day number to travel data

This isn't particularly elegant, but should work, assuming your dates / times are all stored as Excel dates / times (i.e. numeric values) and NOT as text strings.

Put this formula in cell A2, and copy down.

=IF(LEFT(B1,7)="Segment",1,IF(ROUNDDOWN(B2,0)=ROUNDDOWN(B1,0),A1,A1+1))

If your dates / times ARE all stored as text strings, a similar approach can work but you'll need to tweak it a bit.

 

[mtnbaik]

Re: Help assigning day number to travel data

That works perfectly. Thank you!