Why go for a big hairy formula when Bob's approach works fine
=TEXT(ABS(C3/24),"[hh]mmss")&IF(C30,"N","S")
The above will lead with zero
does not put in a negative sign
Regards,
Tom Ogilvy
John Johndon wrote in message
...
Thanks for the help guys, appreciate it!
here is the formula I am using, combining both of your suggestions...
=(INT(ABS(C3))*10000+TEXT(INT(MOD(ABS(C3),1)*60)," 00")*100+INT(MOD(MOD(ABS(C
3),1)*3600,60)))*SIGN(C3)& IF(C3<0,"S","N")
a couple of issues that I have tried to fix and can't seem to
1) I want the degrees portion to always have two digits, ie 5.1234
converted
will read 050724N, how do I force it to lead with 0 (if req'd)?
2) When I use the formula with a negative value, the converted value still
has the negative (-) sign in front, how do I get rid of that?
thanks again
John J
"Bob Phillips" wrote in message
...
John wanted to show -ve as S, +ve as N.
On my post, if you don't want the dots between the degrees, minutes and
secs, use
=TEXT(ABS(A1/24),"[h]mmss") & IF(A1<0,"S","N")
--
HTH
-------
Bob Phillips
... looking out across Poole Harbour to the Purbecks
"Michael Bednarek" wrote in message
...
On Sat, 12 Jul 2003 02:22:46 -0400, "John Johndon"
wrote in microsoft.public.excel.programming:
I am somewhat familiar with VB but never written anything for Excel
at
all,
so please be patient with me.
I need to run a macro or some VBA code to run on an Excel
spreadsheet.
The
spreadsheet consists of a couple of columns that contain latitudes
and
longitudes. The latitudes (and longitudes) are in decimal format e.g
+26.1234 which needs to be converted into a degrees minutes and
seconds
(DMS) format, the + or - sign signals either North or South latitude,
so
the
converted colum would look like 260724N which is 26 degrees, 07
minutes,
and
24 seonds North.
[snip]
No VBA code necessary. A formula I came up with quickly is:
=(INT(ABS(A2))*10000+TEXT(INT(MOD(ABS(A2),1)*60)," 00")*100+INT(MOD(MOD(ABS(A
2),1)*3600,60)))*SIGN(A2)
will show 260724 when A2=26.1234 and -260724 when A2 = -26.1234
Formatting that with a hemisphere is left as an exercise for the
reader.
--
Michael Bednarek, IT Manager, Tactical Global Management
Waterfront Pl, Brisbane 4000, Australia. "POST NO BILLS"
http://mcmbednarek.tripod.com/