[Michael Bednarek]

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(A2),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/

[Bob Phillips[_5_]]

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/

[John Johndon]

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/

[Tom Ogilvy]

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/

[Bob Phillips[_5_]]

John,

This really is overkill. My formula is much simpler, does not have a leading
negative sign, and Tom showed you how to get a leading 0 in the degrees by
adding another digit to the hour format
=TEXT(ABS(A1/24),"[hh]mmss") & IF(A1<0,"S","N")

Bigger is not always better!

--
HTH

-------

Bob Phillips
... looking out across Poole Harbour to the Purbecks


"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/