# Degree minutes seconds multiplication in excel

February 9, 2018 at 07:56:09
Specs: Windows 10
 write now i am doing the below calculation in scientific calculator. in scientific calculator:-8~55’0” * 0~4’0”+SHIFT+DMS The output is 0~35’40”the same i need in excel. could you please provide anybody. thanks in advance.

See More: Degree minutes seconds multiplication in excel

#1
February 9, 2018 at 11:52:17
 See here for a couple of User Defined Functions (UDF) for converting DMS to Decimal and back.https://support.microsoft.com/en-us...You could do the calculations in multiple cells or combine the UDFs as follows:=Convert_Degree(Convert_Decimal("8° 55' 0""")*(Convert_Decimal("0° 4' 0""")))

#2
February 9, 2018 at 13:28:07
 Just for fun, I "upgraded" the process. Try this:In B1 I entered Alt-0176 to get ° In B2 I entered '' to get ' (That's 2 single quotes and a space.)In B3 I entered " to get " (Double quote)In Column A1:A3 and A5:A7 I entered the values shown.In A9 I entered the formula shown.Notes: 1 - In B2 you need to enter 2 single quotes to get one single quote because the first single quote is seen by Excel as a Text designator and does not show in the cell.2 - In B1 and B2, you have to include the space after the ° and ' in order to get the answer that you posted.``` A B 1 8 ° 2 55 ' 3 0 " 4 5 0 6 4 7 0 8 9 =Convert_Degree(Convert_Decimal(A1&B1&A2&B2&A3&B3)*Convert_Decimal(A5&B1&A6&B2&A7&B3)) ```

#3
February 9, 2018 at 17:00:42
 Thank you DerbyDad for giving the answer and it is working fine. thanks alot.--sankar

Related Solutions

#4
February 9, 2018 at 17:58:14
 I'm glad it worked for you. What I find interesting is that if you leave out the spaces, you get a different answer. In fact, if you hadn't of included an example of both input and the expected output, I might have posted a solution that only appeared to work - because it would have returned an answer, albeit an incorrect one.If I was going to use that UDF in a "production environment" I might add some error checking to make sure that the input included spaces to ensure that the correct output was produced every time.

#5
February 10, 2018 at 01:05:15
 Hi Derby,I am using 2nd option which is closed to my requirement. and the output also should be in separate cells.could you please help me.` A B C``1 ° ' "``2 08 55 00``3 00 04 00``4 0 35 40`

#6
February 10, 2018 at 12:33:34
 You may need to play with this to make it work for every situation, but this is the general concept for one method:Degrees:=LEFT(Convert_Degree(Convert_Decimal(A2&A1&B2&B1&C2&C1)*Convert_Decimal(A3&A1&B3&B1&C3&C1)),2)*1Minutes:=MID(Convert_Degree(Convert_Decimal(A2&A1&B2&B1&C2&C1)*Convert_Decimal(A3&A1&B3&B1&C3&C1)),4,3)*1Seconds:=MID(Convert_Degree(Convert_Decimal(A2&A1&B2&B1&C2&C1)*Convert_Decimal(A3&A1&B3&B1&C3&C1)),9,2)*1If you know anything about VBA, you could probably adapt the UDF's to spit out just the Degrees portion, Minutes portion and Seconds portion. In other words, create multiple UDF's, one for each section of the overall answer.