Looking for a formula(s) that will return the next date (higher than the starting date) from a row of dates, then the next date from that one

Question

Worksheet 1 lists all of the "legs" flown by our aircraft. The leg or legs for each day would then go to Worksheet 2 for Day 1, Worksheet 3 for Day 2, and so on. Legs are listed in columns starting with leg 1, then leg 2, and so on. Each leg has a date in the top cell. I'm creating a workbook that will take the leg(s) on each day and transpose the information from that flight to another worksheet (Day 1, Day 2,...). The problem I'm running into is a day's flights could be 1 leg, to 5-6 legs. There are 24 columns that cover a period of about 2 weeks. Each "Day" worksheet only has 8 columns since we'd never fly more than 8 legs in a day. The day 1 worksheet is easy, but on day 2 i run into problems. I'm currently using a combination of IF & AND functions to write the formula to determine which is the next date it should enter. But I just can't get it to work the way i want. I'll try to type out an example below that hopefully clears this muddy water up.

MASTER TRIP WORKSHEET

LEG 1___LEG 2___LEG 3___LEG 4___LEG 5___LEG 6
8/5______8/6______8/6_____8/7______8/7_____8/8
CAK_____PLN_____APA____ASE_____ADS_____DAL
PLN_____APA_____ASE____ADS_____DAL_____SUN

DAY 1 WORKSHEET

LEG 1
8/5
CAK
PLN

DAY 2 WORKSHEET

LEG 2___LEG 3
8/6______8/6
PLN______APA
APA______ASE

DAY 3 WORKSHEET

LEG 4___LEG 5

8/7______8/7
ASE______ADS
ADS______DAL

DAY 4 WORKSHEET

LEG 6
8/8
DAL
SUN

So the Day 1, Day 2... worksheets need to only grab the next NEW dates (and again, this could be 1 to 8 legs on each individual worksheet). I've figured out how to move all the leg information over, i JUST need to be able to separate the dates onto the correct worksheets.

Please let me know if this isn't clear enough. AND THANK YOU!!

Answer 1

Perhaps this will help: on the [Day 1] sheet, cell A2 put this simple formula to pick up the first date from [Sheet1] =Sheet1!A2 Put this formula into [Day 1] sheet at B2 and fill to the right as far as you want to go -- column H?

=IF(COUNTIF(Sheet1!2:2,$A2)>COLUMN()-1,$A2,"")

This will replicate the date in 'Day 1'!A2 as many times as it appears on [Sheet1] which should correspond with Legs. The Column()-1 part of that formula is based on the first formula being in column B, for column B it will return 1, for column c, it will return 2, etc. for the rest of the columns. This is what tells it whether or not to echo the date in A2 on the sheet.

So that should take care of the [Day 1] sheet's dates. Now for [Day 2]. The formula for cell A2 is a bit complex, but what this is doing is testing to see if the date on the previous Day # sheet +1, +2, +3 or +4 is on [Sheet1]. The first one matching that is placed into cell A2. Now, if it shows the text "No Match for previous date +1 to +4" instead of a date, it means 1 of 2 things: either you took longer than a 4-day break between legs, or the last date on [Sheet1] was found on the previous [Day #] sheet. Hope that makes sense. Here's the formula for [Day 2], cell A2:

=IF(ISNA(MATCH(MAX('Day 1'!A2:H2)+1,Sheet1!2:2,0)),IF(ISNA(MATCH(MAX('Day 1'!A2:H2)+2,Sheet1!2:2,0)),IF(ISNA(MATCH(MAX('Day 1'!A2:H2)+3,Sheet1!2:2,0)),IF(ISNA(MATCH(MAX('Day 1'!A2:H2)+4,Sheet1!2:2,0)),"No Match for previous date +1 to +4",MAX('Day 1'!A2:H2)+4),MAX('Day 1'!A2:H2)+3),MAX('Day 1'!A2:H2)+2),MAX('Day 1'!A2:H2)+1)

The formula for the [Day 2] sheet, cells B2:H2 is exactly the same as we used for those cells on the [Day 1] sheet.

For each subsequent [Day #] sheet, you take the long formula above and plug it into cell A2 on that sheet, and use Edit-->Replace to change the 'Day 1' references in it to the previous day's sheet name.

Want to allow for longer than a 4-day break? Best probably to work with a text editor and Excel for this exercise. Take this formula segment:

IF(ISNA(MATCH(MAX('Day 1'!A2:H2)+4,Sheet1!2:2,0)),"No Match for previous date +1 to +4",MAX('Day 1'!A2:H2)+4)

and make these changes to it: First change the 'Day 1' references to the proper reference for the previous day # sheet. Second change the +4 occurrences to +5: there are 3 of them all together. Now take the revised section and select the "No Match for previous date +1 to +4" of the actual formula on the worksheet and paste the revision in place of that!

You could repeat this process to give you really long breaks at the expense of a really long, very ugly formula.

I've uploaded a sample file with 10 [Day #] sheets in it that you should be able to download from here: http://www.mediafire.com/view/k6qx3hahv8zl0o8/AircraftLegsLog001.xlsx