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.knu...@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.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
