Cell formatting question

  • Thread starter Thread starter Sheila Jones
  • Start date Start date
S

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.
 
How do I format cells in degrees/minutes/seconds using Excel 2003?

Excel doesn't have such a number format, sorry.
 
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
 
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
 
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


.
Click to expand...


.
Click to expand...
 
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
 
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
 
Back
Top