My first post to the forum and thank you for reading.
I have the following data which is the itinerary of an air-ticket.
I would like to find out the destination and return date.
itinerary 3.PNG
The destination should be PEK and the return date is 20200405.
Some background:
- Under 1 air-ticket number, there should be by maximum 4 segments. Therefore for any itinerary with over 4 segments, the data from 5th segments will be on the next row with a new conjunction ticket number. Hence the whole itinerary of this case is NRT-MFM-WUX-PEK-MFM-NRT.
- For any segment with no air transportation (such as segment 3 (WUX-PEK)), that will be a VOID segment with no data of date.
The logic is:
- Flight date and origin of segment 4 are said to be the return date and the destination, because the flight date of segment 4 and its previous valid segment (i.e. segment 2) are with the longest gap time.
I wonder what kind of excel formula works best to solve it.
Thank you very much in advance.
Bookmarks