Den sön 22 nov. 2020 kl 11:22 skrev Johnny Rosenberg <gurus.knu...@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 <codecompl...@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+unsubscr...@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+unsubscr...@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
