Google Sheets is changing my data - deg:arcmin:arcsec format

Question

As an astronomer, I've been using Google Sheets to keep track of observation planning. Common practice in astronomy is for degree-based measurements to be written DD:MM:SS, where DD is degrees (0:180, typically) and the latter are arcminutes and arcseconds (1/60th and 1/360th of a degree), respectively. It's also common practice to explicitly display the sign of the measurement, i.e. +12:34:56. This is not really necessary, but it makes things nicer to read to include it.

I've had some trouble pasting these coordinates into Google Sheets, however. Google Sheets will read +12:34:56 as an incomplete formula, and will insert an = to the beginning of the string, leading to a formula error as Google Sheets assumes this to be a malformed range. This can be resolved by removing the =+, effectively converting the value to a duration. This is fine, and I can do math on this.

However, this spawns a further problem. If I paste in a coordinate where the integer value of the degrees is greater than the integer value of the arcminutes, Google Sheets assumes this is also a mistake on my part (ranges do not go backwards) and swaps the two values. Thus, +65:43:21 becomes =+43:65:21, which is a completely different coordinate (in addition to a formula error) and throws off my scheduling significantly.

Is there a way to resolve both of these issues? They are clearly linked - if I can resolve either, I suspect I can resolve both. In any case, the latter issue is most problematic. I would like to avoid resorting to strings, if possible, since I do some math on the resulting values. Forcing Sheets to read a DD:MM:SS value as a duration is acceptable; I've done some workarounds with this in the past. My ideal solution of course would be to teach Google Sheets to simply display a DD:MM:SS value, but allow math on it as if it was the number being represented.

Answer 1

One easy way to tell sheets that something is not a formula is by adding a single quote at the begining of the data, so instead of just writing

+43:65:21

You could write

'+43:65:21

Google Sheets would ignore the single quote and if you were to copy the vales to use anywhere else other than sheets you will get the value without the quote, please give it a shot and let me know if it helped!

Answer 2

Posting an answer to my own question here, to share the solution I came up with. Ultimately, it was a combination of factors. I abandoned displaying the + in my data, as this seemed to be a large contributor to my problems; I've done this by changing the script that outputs data to omit the + when it displays the results. This, combined with data-typing the column for "Duration" (via Format->Number->Duration), has allowed me to retain my "massive copy-paste input method", while keeping number-like values for my equations, and leaving me with a data sheet that is reasonably formatted.

I'm not planning to accept this answer, since it doesn't solve all of the issues raised in my initial question, but I wanted to share my progress for anyone else who does similar work.