Overlapping Flight time

[DrKraGeN]

So let me explain!

DHC-6

Plane Name

s/o

s/o

13:05

13:35

CARGO

Flight Number

Hugo

Pierre

Maxim

D-8(300)

Plane Name

s/o

s/o

13:05

14:15

CARGO

Flight Number

Robert

Gee-C

Alex M

ATR-72

Plane Name

s/o

s/o

13:20

14:30

CARGO

Flight Number

Hugo

Pierre

Maxim

In this exemple i have 3 flight Schedule in the same time range! My question is, would it be possible to have a formula that return a value in a specific cell which would say Conflicted Flight with Flight# ...... when 3 or more flight is in the same time range considering we have 2 team to serve plane at the same time

 

[awoohaw]

Welcome to the forum. Are you only looking to identify the service crew conflicts? (In this case, is there only a conflict with the first and third flights?)

 

[DrKraGeN]

Welcome to the forum. Are you only looking to identify the service crew conflicts? (In this case, is there only a conflict with the first and third flights?)

Thank you!!

In some way! i have multiple flights a day(8 to 16 flights) that required 1 team per flight, we only have 2 team and that mean that only 2 plane can be done at the same time. Usually the flights are planed taking that in consideration and are schedule that not more than 2 flights occur at the same time but some other instances there is some that are conflicted which mean that more than 2 flight are at the same time.
In the exemple they are all conflicting because there is more than 2 flight in the same time range

 

[DrKraGeN]

Thank you!!

In some way! i have multiple flights a day(8 to 16 flights) that required 1 team per flight, we only have 2 team and that mean that only 2 plane can be done at the same time. Usually the flights are planed taking that in consideration and are schedule that not more than 2 flights occur at the same time but some other instances there is some that are conflicted which mean that more than 2 flight are at the same time.
In the exemple they are all conflicting because there is more than 2 flight in the same time range

Planification 24 au 30 juin 2024 (1).xlsx
	A	B	C	D	E						K	L	M				Q	R	S	T	U	V		X	Y	Z
36		3H		ATR-72	C-GWRD						s/o	s/o	14:55												GPU	PRIO PAX
37	CARGO		9886										16:05				YGW			Jason	Gee-C	Cantave				leave one guy on ATR for jet
38		3H		D-8(300)	C-FAID						s/o	s/o	15:45												GPU
39	CARGO		9887										17:00				YGW	YUD		Robert	Anthony	Pierre
40		3H		B-737(200)	C-GMAI						1	0	15:40				YGL				Ail-1	Kevin		2P/76Y		1 PBM OUT 1 PBM IN
41	PAX		705										16:45				YUL			Jason	Gee-C	Cantave				NO BELLY ??
Mardi

Cell Formulas
Range	Formula
K36,K38	K36	=IFERROR(VLOOKUP(E36,'Banque de données'!A:F,5,FALSE),"")
L36,L38	L36	=IFERROR(VLOOKUP(E36,'Banque de données'!A:F,6,FALSE),"")
A36:A41	A36	=IFERROR(VLOOKUP(C36,'Banque de données'!G:H,2,FALSE),"")
B36,B38,B40	B36	=IFERROR(VLOOKUP(E36,'Banque de données'!A:F,3,FALSE),"")
T37,T41,T39	T37	=IF($AF36="","",IF($AF36="Équipe A",AI$10,IF($AF36="Équipe B",$AI$11,$AI$12)))
U37,U41,U39	U37	=IF($AF36="","",IF($AF36="Équipe A",$AJ$10,IF($AF36="Équipe B",$AJ$11,$AJ$12)))
V37,V41,V39	V37	=IF($AF36="","",IF($AF36="Équipe A",$AK$10,IF($AF36="Équipe B",$AK$11,IF($AF36="Équipe C",$AK$12,""))))
D36,D38,D40	D36	=IFERROR(VLOOKUP(E36,'Banque de données'!A:F,2,FALSE),"")
Y40	Y40	=IF(E40="C-GAIG","Escalier","")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
M7,M9,M11,M13,M15,M17,M19,M21,M23,M25,M27,M29,M31,M33,M35,M37,M39,M41,M43,M45,M47	Expression	=$B6="HQ"	text	NO
M6,M8,M10,M12,M14,M16,M18,M20,M22,M24,M26,M28,M30,M32,M34,M36,M38,M40,M42,M44,M46	Expression	=$B6="HQ"	text	NO
B6:B47	Cell Value	="HQ"	text	NO
Z32:Z40	Cell Value	contains "Responsable"	text	NO
C6:C9,C34:C41	Cell Value	duplicates	text	NO

Cells with Data Validation
Cell	Allow	Criteria
A36:A41	List	='Banque de données'!$L$2:$L$8
E36:E41	List	='Banque de données'!$A:$A

My cells reference would be M rows for the time

here a better exemple expemple!

 

[DrKraGeN]

What i come up so far is not properly highlighting all the flight that are conflicting
Plus, when plane are already on ground they have a "-" at the arrival time and it doesnt seem to calculate properly either!

Sub HighlightOverlappingFlights()
    Dim ws As Worksheet
    Dim rngArrivals As Range, rngDepartures As Range
    Dim arrivalCell As Range, departureCell As Range
    Dim overlapCount As Long
    Dim i As Long
    
    On Error Resume Next
    
    Set ws = ThisWorkbook.Sheets("Monday")
    
    If ws Is Nothing Then
        
        Exit Sub
    End If
    
    Set rngArrivals = ws.Range("M6:M46")
    Set rngDepartures = ws.Range("M7:M47")
    
    ws.Cells.FormatConditions.Delete
    
    For i = 1 To rngArrivals.Rows.Count Step 2 ' Increment of 2 for arrivals and departures
        Set arrivalCell = rngArrivals.Cells(i, 1)
        Set departureCell = rngDepartures.Cells(i, 1)
        
        If Not IsEmpty(arrivalCell.Value) And Not IsEmpty(departureCell.Value) _
           And IsNumeric(arrivalCell.Value) And IsNumeric(departureCell.Value) Then
          
            Dim adjustedArrival As Date
            If Left(arrivalCell.Value, 1) = "-" Then
                adjustedArrival = departureCell.Value - TimeValue("1:30:00")
            Else
                adjustedArrival = arrivalCell.Value
            End If
            
            overlapCount = 0
            
            ' Loop through again to compare with other flights
            For Each departureCell In rngDepartures.Cells
                If Not IsEmpty(departureCell.Value) And IsNumeric(departureCell.Value) Then
                    If departureCell.Value >= adjustedArrival And departureCell.Offset(0, -1).Value = "" Then
                        overlapCount = overlapCount + 1
                        If overlapCount >= 3 Then
                            arrivalCell.Interior.Color = RGB(255, 0, 0)
                            Exit For
                        End If
                    End If
                End If
            Next departureCell
            
        End If
    Next i
    
    On Error GoTo 0 ' Reset error handling
    
End Sub

 

[DrKraGeN]

What i come up so far is not properly highlighting all the flight that are conflicting
Plus, when plane are already on ground they have a "-" at the arrival time and it doesnt seem to calculate properly either!

Sub HighlightOverlappingFlights()
    Dim ws As Worksheet
    Dim rngArrivals As Range, rngDepartures As Range
    Dim arrivalCell As Range, departureCell As Range
    Dim overlapCount As Long
    Dim i As Long
   
    On Error Resume Next
   
    Set ws = ThisWorkbook.Sheets("Monday")
   
    If ws Is Nothing Then
       
        Exit Sub
    End If
   
    Set rngArrivals = ws.Range("M6:M46")
    Set rngDepartures = ws.Range("M7:M47")
   
    ws.Cells.FormatConditions.Delete
   
    For i = 1 To rngArrivals.Rows.Count Step 2 ' Increment of 2 for arrivals and departures
        Set arrivalCell = rngArrivals.Cells(i, 1)
        Set departureCell = rngDepartures.Cells(i, 1)
       
        If Not IsEmpty(arrivalCell.Value) And Not IsEmpty(departureCell.Value) _
           And IsNumeric(arrivalCell.Value) And IsNumeric(departureCell.Value) Then
         
            Dim adjustedArrival As Date
            If Left(arrivalCell.Value, 1) = "-" Then
                adjustedArrival = departureCell.Value - TimeValue("1:30:00")
            Else
                adjustedArrival = arrivalCell.Value
            End If
           
            overlapCount = 0
           
            ' Loop through again to compare with other flights
            For Each departureCell In rngDepartures.Cells
                If Not IsEmpty(departureCell.Value) And IsNumeric(departureCell.Value) Then
                    If departureCell.Value >= adjustedArrival And departureCell.Offset(0, -1).Value = "" Then
                        overlapCount = overlapCount + 1
                        If overlapCount >= 3 Then
                            arrivalCell.Interior.Color = RGB(255, 0, 0)
                            Exit For
                        End If
                    End If
                End If
            Next departureCell
           
        End If
    Next i
   
    On Error GoTo 0 ' Reset error handling
   
End Sub

and it doesnt apply automaticaly when i put new flight i need to run the code each time i wanna know what in overlapping

 

[Eric W]

How about:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
35															Overlapping flights
36					C-GWRD								14:55
37			9886										16:05		9887, 705
38					C-FAID								15:45
39			9887										17:00		9886, 705
40					C-GMAI						1	0	15:40
41			705										16:45		9886, 9887
Sheet2

Cell Formulas
Range	Formula
O36:O41	O36	=IF(C36="","",LET(flights,$C$36:$C$41,times,$M$36:$M$41,start,FILTER(times,MOD(ROW(times),2)=0),end,FILTER(times,MOD(ROW(times),2)=1),flts,FILTER(flights,flights<>""),overlap,IF(M36<end,M36,end)-IF(M35>start,M35,start),TEXTJOIN(", ",1,FILTER(flts,(flts<>C36)*(overlap>0),""))))

You can set up Conditional Formatting to look for a comma in the O column, which would indicate at least 2 overlapping flights.