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.knu...@gmail.com >: > 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
