How to write the degree, minute second format in excel

Jamal NUMAN · #1 06-10-2012, 12:23 PM

Quote:

Originally Posted by Ronaldo

How to write 30.89 degree in degree minute and second. I did not understand the above format.

Hi,

Please, get back to the thread below. Paul has thoroughly elaborated regarding this issue.

https://www.msofficeforums.com/excel...html#post34536

Best

Jamal

fzuazua · #2 06-17-2013, 10:11 PM

If cell “E19” is 78.359272 in degrees decimal then:
CONCATENATE(TEXT(INT(E19),"##° "),TEXT(INT((E19-INT(E19))*60),"##'"),TEXT(MOD((E19-INT(E19))*60,INT((E19-INT(E19))*60))*60,"##.##"),"""")
Will return:
78° 21' 33.38"
It may seem complicated but it give the exact format to put in to Google Earth or similar software. U can do all your calculations in decimal. If u use radians u must convert to degrees before applying the formula.

Jamal NUMAN · #3 06-24-2013, 11:52 AM

Quote:

Originally Posted by fzuazua

If cell “E19” is 78.359272 in degrees decimal then:
CONCATENATE(TEXT(INT(E19),"##° "),TEXT(INT((E19-INT(E19))*60),"##'"),TEXT(MOD((E19-INT(E19))*60,INT((E19-INT(E19))*60))*60,"##.##"),"""")
Will return:
78° 21' 33.38"
It may seem complicated but it give the exact format to put in to Google Earth or similar software. U can do all your calculations in decimal. If u use radians u must convert to degrees before applying the formula.

Thanks fzuazua. Sounds to be cool. I’ll be trying it and see if it works with me

Best

Jamal

Jamal NUMAN · #4 07-26-2013, 05:23 AM

Quote:

Originally Posted by fzuazua

If cell “E19” is 78.359272 in degrees decimal then:
CONCATENATE(TEXT(INT(E19),"##° "),TEXT(INT((E19-INT(E19))*60),"##'"),TEXT(MOD((E19-INT(E19))*60,INT((E19-INT(E19))*60))*60,"##.##"),"""")
Will return:
78° 21' 33.38"
It may seem complicated but it give the exact format to put in to Google Earth or similar software. U can do all your calculations in decimal. If u use radians u must convert to degrees before applying the formula.

Hi fzuazua,

The code is working fine but sounds that it is for displaying purposes only. No calculations can be made!

So, for example, if the angles are required to be summed, then this operation can’t be performed

Is there still a way to have the correct display while the operations can also be preformed

Other relevant threads are

From decimal to degree
https://www.msofficeforums.com/excel...html#post50083

Angles calculations
https://www.msofficeforums.com/excel...lculation.html

Best

Jamal

Bovski · #5 07-09-2018, 05:44 PM

Quote:

Originally Posted by fzuazua

If cell “E19” is 78.359272 in degrees decimal then:
CONCATENATE(TEXT(INT(E19),"##° "),TEXT(INT((E19-INT(E19))*60),"##'"),TEXT(MOD((E19-INT(E19))*60,INT((E19-INT(E19))*60))*60,"##.##"),"""")
Will return:
78° 21' 33.38"
It may seem complicated but it give the exact format to put in to Google Earth or similar software. U can do all your calculations in decimal. If u use radians u must convert to degrees before applying the formula.

First off Sorry for dragging up an old subject but google seemed to think it was relevant.

Thanks that sort of got me there it is a bit broken from when there was just seconds or parts of Seconds.

=CONCATENATE(TEXT(INT(E19),"00° "),TEXT(INT((E19-INT(E19))*60),"00' "),TEXT(MOD((E19*60*60),"1"),"00.000############## ##################"),"""")

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Excel 2007 cell format help!!!	Raner	Excel	4	06-17-2013 11:13 PM
Excel convert format [h]:mm:ss to decimal	gchan2000	Excel	1	08-17-2010 01:36 PM
Updating excel link alters table format in PP	dbmagnus	PowerPoint	0	06-03-2010 12:12 PM
Need help in format or link in excel	handsome1968	Excel	1	05-01-2010 09:20 AM
Format Excel to look like Word	Kilconey	Excel	1	04-30-2010 09:42 AM