Decimal numbers in Excel
 

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°1710€�), 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 doesnt require the SEPARATION of a number.

Decimal numbers in Excel
 

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

"Doc" wrote:

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°1710€�), 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 doesnt require the SEPARATION of a number.

Decimal numbers in Excel
 

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


"Mike H" wrote:

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

"Doc" wrote:

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°1710€�), 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 doesnt require the SEPARATION of a number.

Decimal numbers in Excel
 

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"

In article ,
Doc wrote:

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°1710€�), 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 doesnt require the SEPARATION of a number.

Decimal numbers in Excel
 

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)


On Sat, 17 Jan 2009 13:07:01 -0800, Doc
wrote:

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.

Decimal numbers in Excel
 

Hmm,

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

Mike

"JE McGimpsey" wrote:

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"

In article ,
Doc wrote:

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.

Decimal numbers in Excel
 

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)



On Sat, 17 Jan 2009 13:07:01 -0800, Doc
wrote:

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.

Decimal numbers in Excel
 

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




"Mike H" wrote:

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

"Doc" wrote:

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°1710€�), 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 doesnt require the SEPARATION of a number.

Decimal numbers in Excel
 

It worked GREAT.
Thanks for your help.

Doc




"JE McGimpsey" wrote:

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"

In article ,
Doc wrote:

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.

Decimal numbers in Excel
 

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

Doc



"Chip Pearson" wrote:

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)


On Sat, 17 Jan 2009 13:07:01 -0800, Doc
wrote:

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°1710€�), 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 doesnt require the SEPARATION of a number.

Decimal numbers in Excel
 

XL stores times as fractional days, so 12:00 noon =0.5, 6:00 pm = 0.75,
etc.

In article ,
Doc wrote:

I'm curious though, where did you come up with the "24" for the divisor??

Decimal numbers in Excel
 

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




"JE McGimpsey" wrote:

XL stores times as fractional days, so 12:00 noon =0.5, 6:00 pm = 0.75,
etc.

In article ,
Doc wrote:

I'm curious though, where did you come up with the "24" for the divisor??

Decimal numbers in Excel
 

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.




In article ,
Doc wrote:

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




"JE McGimpsey" wrote:

XL stores times as fractional days, so 12:00 noon =0.5, 6:00 pm = 0.75,
etc.

In article ,
Doc wrote:

I'm curious though, where did you come up with the "24" for the divisor??

Decimal numbers in Excel
 

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


Doc





"JE McGimpsey" wrote:

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.




In article ,
Doc wrote:

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




"JE McGimpsey" wrote:

XL stores times as fractional days, so 12:00 noon =0.5, 6:00 pm = 0.75,
etc.

In article ,
Doc wrote:

I'm curious though, where did you come up with the "24" for the divisor??

Decimal numbers in Excel
 

You might also be interested in www.cpearson.com/Excel/LatLong.aspx
for formulas that work with latitude and longitude and Great Circle
Distances.

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

On Sat, 17 Jan 2009 15:22:11 -0800, Doc
wrote:

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

Doc



"Chip Pearson" wrote:

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)


On Sat, 17 Jan 2009 13:07:01 -0800, Doc
wrote:

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.