Excel - Decimal degrees to Degrees minutes decimals.. help :)

Slackjaw190 · 26 Jan 2009 at 18:37

Hi

I have a list of decimal degrees and I want to change them to Degrees minutes and decimals e.g. 5·00 = 5°00`·00 in excel.

I got the following formula
=IF(A1<0,"-"&TEXT(ABS(A1)/24,"[hh]\ºmm\'ss\"""),TEXT(A1/24,"[hh]\ºmm\'ss\""")) which give degrees minutes seconds but i want the seconds as a decimal.

I know it probably quite simple but I am dumb and help will be much appreciated

Thanks

Antar Bolaeisk · 26 Jan 2009 at 23:14

Perhaps you could siphon out the seconds, round them to an integer and then build a string with all the necessary components joined together again?

Simply hide all the cells with the multiple formula in it after that.

(Probably an easier way though)

Slackjaw190 · 27 Jan 2009 at 10:57

yeah thats what i have done though it quite long winded. the problem is seconds are time and there are no decimals in time.

Im sure there must be an easier way.

Thanks

Antar Bolaeisk · 27 Jan 2009 at 12:47

Perhaps something like this might work for you?

Not sure if it's what you mean though.

Those last two columns pull out the seconds and then round them off to two places.

Slackjaw190 · 27 Jan 2009 at 13:53

Yeah that’s kind of what I have done, thou your looks a lot neater and a lot less columns.
Going to see if I can tidy mine up in a bit and get it looking as tidy as yours

Thanks

Antar Bolaeisk · 27 Jan 2009 at 13:55

I'll upload mine to skydrive if you want to take a look at it, all those nasty columns can just be hidden as well if need be.

Here it is

Slackjaw190 · 27 Jan 2009 at 14:53

lol just kinda got it working myself apart from the last column.

I had it so the data now started in A3 instead of A2 and the cells B2,D2 & G2 contains Deg, Min•, Sec respectively as is the only way I could think how to get the answer but it is not working properly.
In the answer box I used the following code
=B3&$B$2&D3&$D$2&$G$2&G3

Was getting 5 Deg 53 Min•399999999999999999999999Sec which aint really any good. I want 5 Deg 53 Min.40Sec. I dunno why I was getting this as G3 = 40

thanks for your help mate got there in the end

Antar Bolaeisk · 27 Jan 2009 at 15:11

Try =round(g3,0)

You're welcome, glad to hear you got it sorted.

Slackjaw190 · 27 Jan 2009 at 18:15

yeah that works. sorry for being so shocking

again thanks for your help

Antar Bolaeisk · 27 Jan 2009 at 19:11

Don't worry you're welcome, everyone learns somewhere and if you don't ask you never learn.

Besides, was a chance to show off what little excel Skillzzzz i has

dan^uk · 27 Jan 2009 at 19:19

I've used this before: =IF(A1<0,"-"&TEXT(ABS(A1)/24,"[hh]\º mm\' ss\"""),TEXT(A1/24,"[hh]\º mm\' ss\"""))