Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Closed Thread
 
Thread Tools Display Modes
  #1  
Old 01-11-2011, 06:14 AM
Jamal NUMAN Jamal NUMAN is offline How to write the degree, minute second format in excel Windows XP How to write the degree, minute second format in excel Office 2007
Expert
How to write the degree, minute second format in excel
 
Join Date: Nov 2010
Posts: 519
Jamal NUMAN is on a distinguished road
Default How to write the degree, minute second format in excel

Hello everybody,
Please, see the attached image that shows my question
What I need is to write the degree, minute second format (which is used for example to measure the angles or geographic coordinates)
Normally, it’s written in the following format:
70 12’ 13’’
Thank you

Attached Images
File Type: jpg Cliptt_4.jpg (97.5 KB, 123 views)
  #2  
Old 01-12-2011, 06:35 PM
macropod's Avatar
macropod macropod is offline How to write the degree, minute second format in excel Windows 7 32bit How to write the degree, minute second format in excel Office 2000
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

You can use a custom number format for the cell:
[h]° mm' ss''
The angle is input in decimal format and the 'º' is created via Alt-0186 (though you should be able to copy & paste the format from this post).
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
  #3  
Old 01-12-2011, 06:50 PM
Jamal NUMAN Jamal NUMAN is offline How to write the degree, minute second format in excel Windows XP How to write the degree, minute second format in excel Office 2007
Expert
How to write the degree, minute second format in excel
 
Join Date: Nov 2010
Posts: 519
Jamal NUMAN is on a distinguished road
Lightbulb

thank u for the answer.

the problem now is how to write the angle in this formt!!! there's no relation with what we type to what is displayed!!

secondlay, when i tried to get the sin or cos of the angles, the answer is wrong!!!

is it just for display?

please, see the attched image

regards





Quote:
Originally Posted by Jamal NUMAN View Post


Hello everybody,



Please, see the attached image that shows my question



What I need is to write the degree, minute second format (which is used for example to measure the angles or geographic coordinates)



Normally, it’s written in the following format:



70 12’ 13’’



Thank you
Attached Images
File Type: jpg Clmmip_10.jpg (111.0 KB, 101 views)
  #4  
Old 01-12-2011, 07:03 PM
macropod's Avatar
macropod macropod is offline How to write the degree, minute second format in excel Windows 7 32bit How to write the degree, minute second format in excel Office 2000
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

Hi Jamal,

For display purposes, you can divide the angle by 24.
For trig purposes, you can multiply the display value by 24.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
  #5  
Old 06-09-2012, 07:21 PM
Ronaldo Ronaldo is offline How to write the degree, minute second format in excel Windows XP How to write the degree, minute second format in excel Office 2007
Novice
 
Join Date: Jun 2012
Posts: 1
Ronaldo is on a distinguished road
Default

How to write 30.89 degree in degree minute and second. I did not understand the above format.
  #6  
Old 06-09-2012, 08:13 PM
macropod's Avatar
macropod macropod is offline How to write the degree, minute second format in excel Windows 7 64bit How to write the degree, minute second format in excel Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

What don't you understand? Have you applied the custom format? Have you looked at the attachments?
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
  #7  
Old 06-10-2012, 12:23 PM
Jamal NUMAN Jamal NUMAN is offline How to write the degree, minute second format in excel Windows 7 64bit How to write the degree, minute second format in excel Office 2010 64bit
Expert
How to write the degree, minute second format in excel
 
Join Date: Nov 2010
Posts: 519
Jamal NUMAN is on a distinguished road
Lightbulb

Quote:
Originally Posted by Ronaldo View Post
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
  #8  
Old 06-17-2013, 10:11 PM
fzuazua fzuazua is offline How to write the degree, minute second format in excel Windows XP How to write the degree, minute second format in excel Office 2003
Novice
 
Join Date: Jun 2013
Posts: 2
fzuazua is on a distinguished road
Default Decimal degrees to Deg Min Sec

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.
  #9  
Old 06-24-2013, 11:52 AM
Jamal NUMAN Jamal NUMAN is offline How to write the degree, minute second format in excel Windows 7 64bit How to write the degree, minute second format in excel Office 2010 64bit
Expert
How to write the degree, minute second format in excel
 
Join Date: Nov 2010
Posts: 519
Jamal NUMAN is on a distinguished road
Thumbs up

Quote:
Originally Posted by fzuazua View Post
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
  #10  
Old 07-26-2013, 05:23 AM
Jamal NUMAN Jamal NUMAN is offline How to write the degree, minute second format in excel Windows 7 64bit How to write the degree, minute second format in excel Office 2010 64bit
Expert
How to write the degree, minute second format in excel
 
Join Date: Nov 2010
Posts: 519
Jamal NUMAN is on a distinguished road
Question

Quote:
Originally Posted by fzuazua View Post
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
Attached Images
File Type: jpg Clip_444.jpg (72.2 KB, 79 views)
File Type: jpg Clip_445.jpg (61.7 KB, 75 views)
  #11  
Old 07-09-2018, 05:44 PM
Bovski Bovski is offline How to write the degree, minute second format in excel Windows 7 64bit How to write the degree, minute second format in excel Office 2016
Novice
 
Join Date: Jul 2018
Posts: 1
Bovski is on a distinguished road
Default

Quote:
Originally Posted by fzuazua View Post
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############## ##################"),"""")
  #12  
Old 07-09-2018, 10:10 PM
ArviLaanemets ArviLaanemets is offline How to write the degree, minute second format in excel Windows 8 How to write the degree, minute second format in excel Office 2016
Expert
 
Join Date: May 2017
Posts: 869
ArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud of
Default

Any MS Office program doesn't support degree format per se.

Macropod's solution assumes the numbers the format is applied to are dates (what is not in your case), and handles them accordingly;
All other solutions convert the value to text string -and are only working solutions for you.

You must have 2 columns - one for degree values, another for display. In case degrees are calculated only, you can hide the real degree column. Whenever you need to refer coordinates in some formula, you have to refer to degree column, not to degree display column.
  #13  
Old 07-09-2018, 11:44 PM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline How to write the degree, minute second format in excel Windows 7 64bit How to write the degree, minute second format in excel Office 2010 64bit
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,766
Pecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant future
Default

@Bovski
Hi and welcome,

Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.
Thread closed
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post
Closed Thread

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
How to write the degree, minute second format in excel Excel 2007 cell format help!!! Raner Excel 4 06-17-2013 11:13 PM
How to write the degree, minute second format in excel 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
How to write the degree, minute second format in excel Format Excel to look like Word Kilconey Excel 1 04-30-2010 09:42 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 04:04 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft