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))),"")

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

<tbody>
</tbody>

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