Date: prev next · Thread: first prev next last
2020 Archives by date, by thread · List index 

Time conversion of input makes the calculation simpler, but 
doesn't handle the values that should be negative. Also, some of 
the sample values had 3 digit values, but when entered as time 
value it adjust values?

I worked with it uses the values as strings as was shown.

00°05'12"O 42°59'12"N   00°05'12"O       42°59'12"N
        -0.086666666666667      42.9866666666667
03°15'090"E 43°12'814"N 03°15'090"E     43°12'814"N
        3.275   43.4261111111111

Column A has the original formatted examples of data.
Column B pulls the first value
=LEFT(INDIRECT(ADDRESS(ROW(),COLUMN()-1)),FIND(" 
",INDIRECT(ADDRESS(ROW(),COLUMN()-1)))-1)
Column C pulls the second value
=MID(INDIRECT(ADDRESS(ROW(),COLUMN()-2)),FIND(" 
",INDIRECT(ADDRESS(ROW(),COLUMN()-2)))+1,20)
Column D converts value in Column B
=(VALUE(MID(INDIRECT(ADDRESS(ROW(),COLUMN()-2)),
1,FIND("°",INDIRECT(ADDRESS(ROW(),COLUMN()-2)))-1))
+VALUE(MID(INDIRECT(ADDRESS(ROW(),COLUMN()-2)),
FIND("°",INDIRECT(ADDRESS(ROW(),COLUMN()-2)))+1,FI
ND(CHAR(39),INDIRECT(ADDRESS(ROW(),COLUMN()-2)))-
FIND("°",INDIRECT(ADDRESS(ROW(),COLUMN()-2)))-1))/6
0+VALUE(MID(INDIRECT(ADDRESS(ROW(),COLUMN()-2))
,FIND(CHAR(39),INDIRECT(ADDRESS(ROW(),COLUMN()-2
)))+1,FIND(CHAR(34),INDIRECT(ADDRESS(ROW(),COLUM
N()-2)))-FIND(CHAR(39),INDIRECT(ADDRESS(ROW(),COLU
MN()-2)))-1))/3600)*IF(OR(RIGHT(INDIRECT(ADDRESS(RO
W(),COLUMN()-2)),1)="E",RIGHT(INDIRECT(ADDRESS(RO
W(),COLUMN()-2)),1)="N"),1,-1)
Column E converts value in Column C
=(VALUE(MID(INDIRECT(ADDRESS(ROW(),COLUMN()-2)),
1,FIND("°",INDIRECT(ADDRESS(ROW(),COLUMN()-2)))-1))
+VALUE(MID(INDIRECT(ADDRESS(ROW(),COLUMN()-2)),
FIND("°",INDIRECT(ADDRESS(ROW(),COLUMN()-2)))+1,FI
ND(CHAR(39),INDIRECT(ADDRESS(ROW(),COLUMN()-2)))-
FIND("°",INDIRECT(ADDRESS(ROW(),COLUMN()-2)))-1))/6
0+VALUE(MID(INDIRECT(ADDRESS(ROW(),COLUMN()-2))
,FIND(CHAR(39),INDIRECT(ADDRESS(ROW(),COLUMN()-2
)))+1,FIND(CHAR(34),INDIRECT(ADDRESS(ROW(),COLUM
N()-2)))-FIND(CHAR(39),INDIRECT(ADDRESS(ROW(),COLU
MN()-2)))-1))/3600)*IF(OR(RIGHT(INDIRECT(ADDRESS(RO
W(),COLUMN()-2)),1)="E",RIGHT(INDIRECT(ADDRESS(RO
W(),COLUMN()-2)),1)="N"),1,-1)

Created a macro that automatically does this. Have value in 
Column A in the text format, and run macro in column B. It then 
does all the formulas.
It does create the negative values if values are not N or E..

Converting the data in column A to Time format is interesting. 

Noticed some of values have 3 digit values.
43°12'814"N
When one enters it as time, it changes it to 
43°25'34"
Not sure if they are equivalent, or if the original data was in error.

Macro was a pain to create. If values would also be fixed 2 digit 
numbers, it is also much simpler, since no need for find..

Interesting to play with...


On 22 Nov 2020 at 12:43, Johnny Rosenberg wrote:

From:                   Johnny Rosenberg 
<gurus.knugum@gmail.com>
Date sent:              Sun, 22 Nov 2020 12:43:35 +0100
Subject:                Re: [libreoffice-users] [Calc] Convert GPS 
coords from DMS to
        decimals?
To:                     LibreOffice Användare 
<users@global.libreoffice.org>


And this is the fourth and last reply (I hope), unless there are follow-up
questions...

I just realised that there are actually dedicated UNICODE characters for
minutes and seconds, and they are U+2032 for minutes (and also feet), and
U+2033 for seconds (and also inches), so this would probably be more
correct:
[TT]"°"MM"´"SS"´´O"
[TT]"°"MM"´"SS"´´N"

Result:
00°05´12´´O
42°58´12´´N

And yhou can have decimals for your seconds too, of course:
[TT]"°"MM"´"SS,00"´´O"
[TT]"°"MM"´"SS,00"´´N"

Or, if a period is used for decimals in your language:
[TT]"°"MM"´"SS.00"´´O"
[TT]"°"MM"´"SS.00"´´N"

Result in my case, after inputting 0:5:15,53 and 42:48:12,8:
00°05´12,53´´O 42°58´12,80´´N
42,97022222°, 0,08681389°



Kind regards

Johnny Rosenberg


Den sön 22 nov. 2020 kl 12:30 skrev Johnny Rosenberg <gurus.knugum@gmail.com

:



Ha ha ha... this time I also looked at your original link. The image there
uses both E and O. Do they mean the same or else, what do they mean? Is E
for East and O for West or maybe the other way around? Anyway, in my
examples, just input a positive number for East and a negative number for
West and replace the O in my example with whatever means East.
Same goes for North and South, of course. A negative number means south, a
positive number means north. Replace the N in my example with whatever
means North in your language.



Kind regards

Johnny Rosenberg

Den sön 22 nov. 2020 kl 12:23 skrev Johnny Rosenberg <
gurus.knugum@gmail.com>:


Den sön 22 nov. 2020 kl 11:22 skrev Johnny Rosenberg <
gurus.knugum@gmail.com>:


Yes, it's very, very easy (when you know how to do it...). Those
coordinates work exactly like time, so all you need to do is to format your
input cells properly (if you care about looks) and multiply your input
cells with 24 (hours per day) in your output cells, because when working
with time in Calc (or Excel or any other spreadsheet application), the
result is in days, so 0,5 (or 0.5 if you use a period for the decimal
symbol) means 12:00:00, 0,75 is 18:00:00 and so on.

Follow this for a demo:

   1. Highlight A1 and right click and click "Format cells...".
   2. Click the "Numbers" tab.
   3. In the "Category" field, select Time and in the format Field
   select the line that looks something like "13:37:46".
   4. Now, in the "Format code" field, replace the colons (or whatever
   they are in your case; it's language dependent) with degrees and the other
   characters inside double quotes, and also make sure your hours symbol is
   inside [], which means it won't flip over to 0 for greater numbers than 23.
   In my case (Swedish), it looks like: "N"[TT]"°"MM"'"SS""". If English
   (USA): "N"[HH]"°"MM"'"SS""". There is a field right under the Language
   selection that gives you an example of what the result would look like. In
   my case it reads: N13°37'46".
   5. Hit "OK".
   6. Repeat steps 1 to 5, but highlight B1 instead of A1 and replace
   "N" with "E" in the "Format code" field.
   7. In A2, type: =A1*24
   8. In B2, type: =B1*24 (or just auto-fill from A2 or even copy and
   paste A2 to B2)
   9. Highlight A2:B2 and increase the number of decimals using the
   ".00+" button or do it in the "Format cells..." dialogue as before by
   entering something like 0,0000000 in the "Format code" field (or 0.0000000
   if your decimal symbol is a period).
   10. Now, in A1, type:
   42:59:12.
   Remember to treat the number as time rather than coordinates.
   Replace ":" with whatever is the appropriate time separator for your
   language.
   11. In B1, type:
   0:5:12

I just read your question again and found that you had it the other way

around (east-west first and then north-south and using O instead of E), so
in your case then:
A1 format code: [HH]"°"MM"'"SS""""O"
B1 format code: [HH]"°"MM"'"SS""""N"
But this won't work, since Calc is not able to figure out all those
double quotes correctly, so my workaround is to use the " double quote
instead (you can copy it from here, if you like, otherwise the UNICODE code
is U+201D. To match that I also use the corresponding ´ single quote, that
is U+2019, so in this case:
A1 format code: [HH]"°"MM"´"SS""O"
B1 format code: [HH]"°"MM"´"SS""N"
You can copy the whole thing from above, of course (and then replace the
letters to what's correct in your selected language).


A2=B1*24
B2=A1*24

The rest should be the same, I guess.




When following my own instructions, here's what my cells look like:
A1
N42°59'12"
B1
E00°05'12"
A2
42,9866666666667
B2
0,086666666666667

You could of course put the both together to a complete text string, but
then you can't easily use them for further calculations. For instance, in
A3, type (for a result with 8 decimals):
=ROUND(A1*24;8) & ", " & ROUND(B1*24;8)


=ROUND(B1*24;8) & ", " & ROUND(A1*24;8)


or, if you want to use the values in A2 and B2:
=ROUND(A2;8) & ", " & ROUND(B2;8)



And you can, of course, also add the degree symbol if you like:
=ROUND(A1*24;8) & "°, " & ROUND(B1*24;8) & "°"



Result (in my case):
42,98666667, 0,08666667



42,98666667°, 0,08666667° after adding the degree symbols.



So, as you see, no advanced formulas are needed at all.


Still correct. 



I hope there were not too many typos above.


Kind regards

Johnny Rosenberg


Den sön 22 nov. 2020 kl 06:14 skrev Gilles <codecomplete@free.fr>:


Hello,

I need to convert GPS coordinates from degrees+minutes+seconds (DMS) to
decimal, eg. 00°05'12"O 42°59'12"N -> 42.98666667,-0.08666667

https://postimg.cc/QH0q5qmn <https://postimg.cc/QH0q5qmn>

Can Calc do this, or should I look elsewhere?

Thank you.



--
Sent from:
http://document-foundation-mail-archive.969070.n3.nabble.com/Users-f1639498.html

--
To unsubscribe e-mail to: users+unsubscribe@global.libreoffice.org
Problems?
https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more:
https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy





-- 
To unsubscribe e-mail to: users+unsubscribe@global.libreoffice.org
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy



+------------------------------------------------------------+
 Michael D. Setzer II - Computer Science Instructor (Retired)     
 mailto:mikes@guam.net                            
 mailto:msetzerii@gmail.com
 Guam - Where America's Day Begins                        
 G4L Disk Imaging Project maintainer 
 http://sourceforge.net/projects/g4l/
+------------------------------------------------------------+




-- 
To unsubscribe e-mail to: users+unsubscribe@global.libreoffice.org
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy

Context

Privacy Policy | Impressum (Legal Info) | Copyright information: Unless otherwise specified, all text and images on this website are licensed under the Creative Commons Attribution-Share Alike 3.0 License. This does not include the source code of LibreOffice, which is licensed under the Mozilla Public License (MPLv2). "LibreOffice" and "The Document Foundation" are registered trademarks of their corresponding registered owners or are in actual use as trademarks in one or more countries. Their respective logos and icons are also subject to international copyright laws. Use thereof is explained in our trademark policy.