Cell formatting question

[Sheila Jones]

Hello,

An easy question (hopefully!): How do I format cells in
degrees/minutes/seconds using Excel 2003? For example, so that 202.5 is
formatted as 202° 30' 0.0".

Thanks.

 

[Jim Rech]

How do I format cells in degrees/minutes/seconds using Excel 2003?

Excel doesn't have such a number format, sorry.

 

[Scott]

Hi Sheila!

The only way I can think of is to take advantage of the
time formats, but everything will be 24 times to big.
Here's what I did. If you've got all of your "rough
formatted" numbers in column A, in B1 put =A1/24 and copy
down. Then choose format -> custom, and put this in
[h]ºmm'ss'' and it'll work.

That's kind of ugly. A better way might be to create a
separate worksheet and link it to the original. Now
anywhere you want your format, just put /24 after the
link in the formula bar and use the same format as above.

Obivously the best way would be if there were some way to
put /24 on the hour in the format window, but I can't
figure that one out. Let me know if you do.

Thanks!
Scott

 

[Sheila Jones]

Hi Scott,

I'd thought about using the time formats too, but couldn't think of anything
neater than your suggestions. Still, it was only a "would be nice to have"
feature, not a showstopper, so I've just left them formatted as 'decimal'
degrees.

I'm surprised that Excel doesn't have this format built in, though.


Hi Sheila!

The only way I can think of is to take advantage of the
time formats, but everything will be 24 times to big.
Here's what I did. If you've got all of your "rough
formatted" numbers in column A, in B1 put =A1/24 and copy
down. Then choose format -> custom, and put this in
[h]ºmm'ss'' and it'll work.

That's kind of ugly. A better way might be to create a
separate worksheet and link it to the original. Now
anywhere you want your format, just put /24 after the
link in the formula bar and use the same format as above.

Obivously the best way would be if there were some way to
put /24 on the hour in the format window, but I can't
figure that one out. Let me know if you do.

Thanks!
Scott

 

[Gord Dibben]

Sheila

Chip Pearson has instructions for this at

http://www.cpearson.com/excel/latlong.htm

Gord Dibben Excel MVP

 

[Sheila Jones]

Thanks for that, there is some useful stuff there.

 

[Scott]

I agree, Excel has so many useful fuctions and other
options, you'd think that this would be included.
Oh well, maybe they'll put it in the next update and make
us pay to get it.
There are more important things in life.
If you ever find a proper way that works, let us know.
Scott

-----Original Message-----
Hi Scott,

I'd thought about using the time formats too, but couldn't think of anything
neater than your suggestions. Still, it was only a "would be nice to have"
feature, not a showstopper, so I've just left them formatted as 'decimal'
degrees.

I'm surprised that Excel doesn't have this format built in, though.


Hi Sheila!

The only way I can think of is to take advantage of the
time formats, but everything will be 24 times to big.
Here's what I did. If you've got all of your "rough
formatted" numbers in column A, in B1 put =A1/24 and copy
down. Then choose format -> custom, and put this in
[h]ºmm'ss'' and it'll work.

That's kind of ugly. A better way might be to create a
separate worksheet and link it to the original. Now
anywhere you want your format, just put /24 after the
link in the formula bar and use the same format as above.

Obivously the best way would be if there were some way to
put /24 on the hour in the format window, but I can't
figure that one out. Let me know if you do.

Thanks!
Scott

-----Original Message----- Excel 2003?

Excel doesn't have such a number format, sorry.

--
Jim Rech
Excel MVP


.

.

 

[bbuubb]

im sorry. i just realized the previous formula is off by a facto
somewhere and it doesnt work for angles less than one degree. ill ge
back to you if i figure it out.

sorr

 

[bbuubb]

ok im back with a longer equation this time. sorry its kinda messy. a
least it works this time:

=IF(A7>=1,(MID(A7*1000000,1,LEN(A7*1000000)-6)+MID((A7+1)*1000000,LEN((A7+1)*1000000)-5,2)/60+MID((A7+1)*1000000,LEN((A7+1)*1000000)-3,4)/360000),MID((A7+1)*1000000,LEN((A7+1)*1000000)-5,2)/60+MID((A7+1)*1000000,LEN((A7+1)*1000000)-3,4)/360000