Creating a Flight Schedule

[Serrax]

Hi everyone, I wonder if someone could help me, I am trying to create a flight schedule in excel, but I am lost on how to do it, I need to create a calendar of flights. I got a list of flights and details o a row, flight number, two range dates, start date and end date, and days of operation, something like this:

A B C D F
1FLIGHT NO. FROM TO DAYS OPERATION DEPARTURE TIME
2 XX145 26/03/18 27/10/18 SUN, MON, TUE, SAT 20:30
3 XX134 26/03/18 20/06/18 MON, WED 07:30

I need to create a season calendar, and translate the data above to a day calendar were I can see the flight details on a daily cell on a different sheet:

A B C D
1 SUN MON TUE WED
2 26/03/18 27/03/18 28/03/18 29/03/18
3 XX145/20:30 XX145/20:30 XX145/20:30 XX134/07:30
XX134/07:30 XX134/07:30

So, not sure if enough clear, but any kind of help will be more than welcome ....
Thanks

 

[markmzz]

Maybe the Array Formula (use Ctrl+Shift+Enter to enter the formula) below can help you:

In A12 and copy down and to the right:

=IFERROR(INDEX($A$2:$A$7&"/"&TEXT($E$2:$E$7,"hh:mm"),
SMALL(IF($B$2:$B$7 <=A$11,IF($C$2:$C$7 >=A$11,
IF(ISNUMBER(SEARCH(", "&A$10&", ",", "&$D$2:$D$7&", ")),ROW($B$2:$B$7)-ROW($B$2)+1))),ROWS(A$12:A12))),"")

[TABLE="class: grid, width: 930"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]FLIGHT NO.[/TD]
[TD]FROM[/TD]
[TD]TO[/TD]
[TD]DAYS OPERATION[/TD]
[TD]DEPARTURE TIME[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]XX145[/TD]
[TD]26/03/2018[/TD]
[TD]29/03/2018[/TD]
[TD]SUN, MON, TUE, SAT[/TD]
[TD]20:30[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]XX134[/TD]
[TD]27/03/2018[/TD]
[TD]20/06/2018[/TD]
[TD]MON, WED[/TD]
[TD]07:30[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]XX135[/TD]
[TD]28/03/2018[/TD]
[TD]21/06/2018[/TD]
[TD]FRI, THU, MON, WED[/TD]
[TD]10:30[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]XX136[/TD]
[TD]29/03/2018[/TD]
[TD]22/06/2018[/TD]
[TD]SUN, MON, WED, SAT[/TD]
[TD]09:30[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]XX146[/TD]
[TD]26/03/2018[/TD]
[TD]29/03/2018[/TD]
[TD]SUN, MON, TUE, SAT[/TD]
[TD]18:30[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]XX147[/TD]
[TD]27/03/2018[/TD]
[TD]20/06/2018[/TD]
[TD]MON, WED[/TD]
[TD]12:30[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]SUN[/TD]
[TD]MON[/TD]
[TD]TUE[/TD]
[TD]WED[/TD]
[TD]THU[/TD]
[TD]FRI[/TD]
[TD]SAT[/TD]
[TD]SUN[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]dom-25/03/2018[/TD]
[TD]seg-26/03/2018[/TD]
[TD]ter-27/03/2018[/TD]
[TD]qua-28/03/2018[/TD]
[TD]qui-29/03/2018[/TD]
[TD]sex-30/03/2018[/TD]
[TD]sáb-31/03/2018[/TD]
[TD]dom-01/04/2018[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD][/TD]
[TD]XX145/20:30[/TD]
[TD]XX145/20:30[/TD]
[TD]XX134/07:30[/TD]
[TD]XX135/10:30[/TD]
[TD]XX135/10:30[/TD]
[TD]XX136/09:30[/TD]
[TD]XX136/09:30[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD][/TD]
[TD]XX146/18:30[/TD]
[TD]XX146/18:30[/TD]
[TD]XX135/10:30[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]XX147/12:30[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]****[/TD]
[TD]*****************[/TD]
[TD]*****************[/TD]
[TD]*****************[/TD]
[TD]********************[/TD]
[TD]*****************[/TD]
[TD]*****************[/TD]
[TD]*****************[/TD]
[TD]*****************[/TD]
[/TR]
</tbody>[/TABLE]

Markmzz

 

[Serrax]

Markmzz, Thanks very much, I have done some changes and works as I want, thanks for your help!!

 

[markmzz]

Markmzz, Thanks very much, I have done some changes and works as I want, thanks for your help!!

You are welcome.

I'm glad to help and thanks for the feedback.

Markmzz