Calculate Bearing or Direction Between 2 Coordinates

[ialvaran]

Good morning to all. After searching this forum and around the internet I can't seem to find a way to create an Excel sheet to calculate bearing or direction between 2 coordinates. I know how to calculate distance but not bearing (direction). There will be a cell for the following in decimal format: LAT1, LON1, LAT2 and LON2. Thank you in advance for all your help.

 

[shg]

Try this:

<TABLE style="WIDTH: 278pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=371><COLGROUP><COL style="WIDTH: 37pt" width=49><COL style="WIDTH: 37pt; mso-width-source: userset; mso-width-alt: 2267" width=50><COL style="WIDTH: 39pt; mso-width-source: userset; mso-width-alt: 2377" width=52><COL style="WIDTH: 37pt" width=49><COL style="WIDTH: 128pt; mso-width-source: userset; mso-width-alt: 7826" width=171><TBODY><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #f3f3f3; WIDTH: 37pt; HEIGHT: 12pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl32 height=16 width=49>Airport</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #f3f3f3; WIDTH: 37pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl32 width=50>Lat</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #f3f3f3; WIDTH: 39pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl32 width=52>Lon</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #f3f3f3; WIDTH: 37pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl32 width=49>Dep Hdg</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: white; WIDTH: 128pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl31 width=171></TD></TR><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: white; HEIGHT: 12pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl31 height=16>DFW</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: white; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl34 align=right>32.8968</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: white; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl34 align=right>-97.0398</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: white; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl33 align=right>104.2</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: white; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl31>D2: =DepHdg(B2,C2,B3,C3)</TD></TR><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: white; HEIGHT: 12pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl31 height=16>MCO</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: white; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl34 align=right>28.4167</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: white; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl34 align=right>-81.3167</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: white; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl31></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: white; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl31></TD></TR></TBODY></TABLE>

Function DepHdg(ByVal lat1 As Double, ByVal lon1 As Double, _
                ByVal lat2 As Double, ByVal lon2 As Double) As Double
    Const pi        As Double = 3.14159265358979
    Const D2R       As Double = pi / 180#
 
    lat1 = D2R * lat1
    lat2 = D2R * lat2
    lon1 = D2R * lon1
    lon2 = D2R * lon2
 
    DepHdg = WorksheetFunction.Atan2(Cos(lat1) * Sin(lat2) - Sin(lat1) * Cos(lat2) * Cos(lon1 - lon2), _
                                     Sin(lon2 - lon1) * Cos(lat2)) / D2R
    If DepHdg < 0 Then DepHdg = DepHdg + 360
End Function

 

[ialvaran]

Thanks for the reply. Unfortunately I am not smart enough to enter this in Excel. How can I make this function in Excel? Sorry for being so ignorant. Thank you.

 

[ialvaran]

I opened a "macro" window then copy - paste your script. It worked! Now I just have to verify the result. Thank you for the help!!! I appreciate it.

 

[shg]

You're welcome, good luck.