Decimal numbers in Excel

D

Doc

Is there a way to separate and use the numbers, to the left of and to the
right of the decimal point in a single cell?
I am trying to convert angles given in decimal form (i.e. 27.286°), to the
older form of degrees, minutes and seconds (i.e. 27°17’10â€), then store the
degrees in one cell, the minutes in another cell, and the seconds in a third.
I can make it work the OTHER way with multiplication and addition, because
that doesn’t require the SEPARATION of a number.
 
M

Mike H

Hi,

Try this to convert decimal degrees to deg, mins & seconds

Alt +F11 to open VB editor, right click 'ThisWorkbook' and insert module and
paste this UDF in

call with

=ConvDeg(a1)

Function ConvDeg(DecAngle) As Variant
Degs = Int(DecAngle)
Mins = (DecAngle - Degs) * 60
secs = Format(((Mins - Int(Mins)) * 60), "0")
ConvDeg = " " & Degs & "° " & Int(Mins) & "' " & secs & """"
End Function

Mike
 
M

Mike H

Ah you wanted the output in 3 different cells and sadly a function cant do
that so 3 seperate formula

Degrees
=INT(A1)

Minutes
=INT((A1-INT(A1))*60)

seconds
=((A1-INT(A1))*60)-INT((A1-INT(A1))*60)

Mike
 
J

JE McGimpsey

One way:

A1: 27.286

if you're interested in numbers:

B1: =INT(A1) --> 27
C1: =MINUTE(A1/24) --> 17
D1: =SECOND(A1/24) --> 10

or if you're just interested in display:

B1: TEXT(A1/24,"[hh]°") --> 27°
C1: =MID(TEXT(A1/24,"h:mm'"),3,3) --> 17'
D1: =TEXT(A1/24,"ss\""") --> 10"
 
C

Chip Pearson

You don't need to use separators. If your decimal degrees (e.g,
27.5111 = 27 deg, 30 min, 40 sec) is in D5, you can use

=INT(D5)
to get the degrees

=MINUTE(D5/24)
to get minutes

=SECOND(D5/24)
to get seconds

If you really want to separate the integral and fractional portions of
D5 into separate cells with a single formula, use the following array
formula:

={1,0}*INT(D5)+{0,-1}*(INT(D5)-D5)

Note the difference between the parens () and the curly braces {}.
Select the cells you want the integral and fractional portions to go
in, type the formula above and press CTRL SHIFT ENTER instead of
ENTER.

Since this is an Array Formula, you *must* press CTRL SHIFT ENTER
rather than just ENTER when you first enter the formula
and whenever you edit it later. If you do this properly,
Excel will display the formula in the Formula Bar enclosed
in curly braces { }. (You do not type the curly braces -
Excel includes them automatically.) The formula will not work
properly if you do not use CTRL SHIFT ENTER. See
http://www.cpearson.com/excel/ArrayFormulas.aspx for lots
more information about array formulas.

You can also use two separate formulas to get the integral and
fractional portions.

=INT(D5)
returns the integral portion

=D5-INT(D5)
returns the fractional portion

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)
 
M

Mike H

Hmm,

I seem to have made very hard work of something you made very simple. Must
try harder!!

Mike

JE McGimpsey said:
One way:

A1: 27.286

if you're interested in numbers:

B1: =INT(A1) --> 27
C1: =MINUTE(A1/24) --> 17
D1: =SECOND(A1/24) --> 10

or if you're just interested in display:

B1: TEXT(A1/24,"[hh]°") --> 27°
C1: =MID(TEXT(A1/24,"h:mm'"),3,3) --> 17'
D1: =TEXT(A1/24,"ss\""") --> 10"

Doc said:
Is there a way to separate and use the numbers, to the left of and to the
right of the decimal point in a single cell?
I am trying to convert angles given in decimal form (i.e. 27.286°), to the
older form of degrees, minutes and seconds (i.e. 27°17’10â€Â), then store the
degrees in one cell, the minutes in another cell, and the seconds in a third.
I can make it work the OTHER way with multiplication and addition, because
that doesn’t require the SEPARATION of a number.
Click to expand...
Click to expand...
 
C

Chip Pearson

In the interest of completeness, you can use the following array
formula in 3 cells to return the degrees, minutes, and seconds to
those cells. Select the 3 cells that should contain the results, type
the formula

={1,0,0}*INT(D5)+{0,1,0}*MINUTE(D5/24)+{0,0,1}*SECOND(D5/24)

and press CTRL SHIFT ENTER. The cells will contain the degrees,
minutes, and seconds of the decimal degrees value in D5. This assumes
that the 3 cells are all on the same line. If they are all in the same
column, over 3 rows, use

=TRANSPOSE({1,0,0}*INT(D5)+{0,1,0}*MINUTE(D5/24)+{0,0,1}*SECOND(D5/24))


Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)
 
D

Doc

Thanks Mike,
I tried but never could get it to work. I kept getting a "compile error".
Probably something I did wrong, I know ABSOUUTELY NOTHING about writing OR
running VB modules.
I did, in the mean time figure out how to do it with the integer function.
Thanks.
Doc
 
D

Doc

It worked GREAT.
Thanks for your help.

Doc




JE McGimpsey said:
One way:

A1: 27.286

if you're interested in numbers:

B1: =INT(A1) --> 27
C1: =MINUTE(A1/24) --> 17
D1: =SECOND(A1/24) --> 10

or if you're just interested in display:

B1: TEXT(A1/24,"[hh]°") --> 27°
C1: =MID(TEXT(A1/24,"h:mm'"),3,3) --> 17'
D1: =TEXT(A1/24,"ss\""") --> 10"

Doc said:
Is there a way to separate and use the numbers, to the left of and to the
right of the decimal point in a single cell?
I am trying to convert angles given in decimal form (i.e. 27.286°), to the
older form of degrees, minutes and seconds (i.e. 27°17’10â€Â), then store the
degrees in one cell, the minutes in another cell, and the seconds in a third.
I can make it work the OTHER way with multiplication and addition, because
that doesn’t require the SEPARATION of a number.
Click to expand...
Click to expand...
 
D

Doc

Thanks Chip,
You guys are all great!
I'm curious though, where did you come up with the "24" for the divisor??

Doc
 
D

Doc

Sorry JE, but I still don't follow. If .5 is noon and .75 equals 6:00 pm,
wouldn't that mean that 24 would be 24 days??

Doc
 
J

JE McGimpsey

Yes, 24 would be 24 days, but when you divide the integer hours (or
their analogue, degrees, in this case), you get a value that XL can
interpret as a time.

24/24 = 1.0 = 24:00 (midnight)

and

12/24 = 0.5 = 12:00 noon.

Your data was in the form 27.286. Since degrees are divisible into
minutes and seconds the same way hours are, you can divide that by 24 to
get 27.386/24-th of a day. You can then tell XL to display it as
[h]:mm:ss, or pass the value on to MINUTE(), SECOND(), etc.
 
D

Doc

Thanks JE, it makes a lot more sense now!
I appreciate all of your help.


Doc





JE McGimpsey said:
Yes, 24 would be 24 days, but when you divide the integer hours (or
their analogue, degrees, in this case), you get a value that XL can
interpret as a time.

24/24 = 1.0 = 24:00 (midnight)

and

12/24 = 0.5 = 12:00 noon.

Your data was in the form 27.286. Since degrees are divisible into
minutes and seconds the same way hours are, you can divide that by 24 to
get 27.386/24-th of a day. You can then tell XL to display it as
[h]:mm:ss, or pass the value on to MINUTE(), SECOND(), etc.




Doc said:
Sorry JE, but I still don't follow. If .5 is noon and .75 equals 6:00 pm,
wouldn't that mean that 24 would be 24 days??

Doc
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Excel does not accept text format! 0
How to count the numbers in a Word document? 0
Converting GPS coordinates to decimal in Report 0
Looking to flip text in Excel file (2010) 0
degrees decimal minutes format for latitude longitude 2
Converting Lat/Lon coordinates to Decimal 4
Access 2007,reports,VBA 2
Converting Decimal Degrees to Degrees/Minutes/Seconds 1

Top