convert decimal degrees to degrees minutes seconds

[Chris r]

im tryin to make a function in a cell and i want to know do i convert decimal
degrees to degrees minutes seconds

thanks

[jlucy]

I've used this:
A B FORMULA IN
COLUMN B
1 Latitude in DD 39.89458898
2 minutes 53.67533862 =(B1-INT(B1))*60
3 seconds 40.5203172 =(B2-INT(B2))*60
4 converted to DMS 39d 53m 40.52032s =CONCATENATE(INT(B1),"d ",INT(B2),"m
",ROUND(B3,5),"s")

you wouldn't need the "d " or "m " or "s" in the concatenate formula if you
don't want or need them, and I wanted the seconds rounded to 5 decimal places
so that is why the ROUND function.

"Chris r" wrote:

> im tryin to make a function in a cell and i want to know do i convert decimal
> degrees to degrees minutes seconds
>
> thanks
>

[Sandy Mann]

AS there are sixty seconds in minutes and seconds in both angles and time
you can use Excels time format:

Divide your decimal angle by 24 and format as [H]:mm-ss

--
HTH

Sandy
sandymann@mailinator.com
Replace@mailinator with @tiscali.co.uk


"Chris r" <Chris r@discussions.microsoft.com> wrote in message
news:DEEA8AA3-8D76-4241-B060-CC18BB07CB24@microsoft.com...
> im tryin to make a function in a cell and i want to know do i convert
> decimal
> degrees to degrees minutes seconds
>
> thanks
>

[srm057]

You can use this to convert DD.DD to DD MM SS

change the format as you want it

Function DD2DMS(Degrees)
DD2DMS = Format(Int(Abs(Degrees)) * Sgn(Degrees), "00") & "° " & Format(60 * (Abs(Degrees) - Abs(Int(Abs(Degrees)) * Sgn(Degrees))), "00") & "' " & Format(60 * (60 * (Abs(Degrees) - Abs(Int(Abs(Degrees)) * Sgn(Degrees))) - Int(60 * (Abs(Degrees) - Abs(Int(Abs(Degrees)) * Sgn(Degrees))))), "00.00") & "''"
End Function

[FDibbins]

srm057, thats a pretty complex looking UDF, but this thread is 8 years old, I doubt they are still monitoring it now

[srm057]

I was looking for the same thing in the morning. I ended up doing myself so i thought I should share it.

this is the easy version of the same function.

Please Login or Register  to view this content.

[arlu1201]

srm057,

Welcome to the forum.

I have added code tags to your post. As per forum rule 3, you need to use them whenever you put any code in your post. Please add them in future. In order to put code tags, either type [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] at the end of it, OR you can highlight your code and click the # icon at the top of your post window.