Excel convert degrees to hrs/mins/secs

R

Robertson1995

Board Regular
Joined
Apr 1, 2009
Messages
117
I need a formula that will convert degrees Lat/Long to hours minutes seconds.

Example:

<TABLE style="WIDTH: 96pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=128 x:str><COLGROUP><COL style="WIDTH: 48pt" span=2 width=64><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=17 width=64 align=right x:num="32.530422219999998">32.53042</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" width=64 align=right x:num="-92.090132999999994">-92.0901</TD></TR></TBODY></TABLE>

Need to convert to:

32 31 49.5114 92 5 24.36 (With spaces in between)
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
Sort by date Sort by votes
Are the latitude and longitude coordinates in separate cells? If so, try:
Code: 
=IF(A1<0,INT(A1)+1&" "&INT(60*MOD(-A1,1))&" "&ROUND(60*MOD(-A1*60,1),4),INT(A1)&" "&INT(60*MOD(A1,1))&" "&ROUND(60*MOD(60*A1,1),4))
 
Upvote 0
Another possible option is to use a formula like:

=A1/24

and then use a custome number format of:

[h] m s.000

in the cell. You do get rounding with this though in the decimal places.
 
Upvote 0
Richard, I like that idea but it freaks out when you input negative numbers. Do you know what may be causing that?
 
Upvote 0
Oh of course - because excel doesn't handle negaive times! Thanks for pointing that out. You could probably deal with that using:

=IF(A1<0,"-","")&TEXT(ABS(A1/24),"[h] m s.000")

but you do still have the rounding.
 
Upvote 0
You must log in or register to reply here.

Similar threads

G
  • Question
Using Excel formula to create HTML code text referencing specific cells
Replies
0
Views
816
gvsumich
G
A
  • Question
VBA - Automatically Break the Line in HTML format.
Replies
7
Views
1K
Attrazion
A
C
  • Solved
Excel 2016 32bit VBA Pulling info from Website to Sheet
Replies
2
Views
1K
CrashOD
C
E
  • Question
Query in excel to Outlook mail take rows with name until next name
Replies
0
Views
344
esben
E
A
  • Solved
Calendar form cancel button passing data to worksheet
Replies
7
Views
379
AndyEd
A

Forum statistics

Threads
1,214,534
Messages
6,120,086
Members
448,944
Latest member
sharmarick

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top