0

This question is close to what I want, however the answers presented there all involve forcing Google Sheets to recognize the elements as strings. This solution does not work for me, however - the numbers in question are declination values (essentially, latitude, but for astronomy), and I need to run some math with them in other cells. So, keeping them as numbers is important. I'd like to be able to simply input them as, e.g.,

+12:34:56

(this format is degree:arcminute:arcsecond), keeping the leading plus sign (preferably visible), without having Google Sheets immediately add an = to the front and then becoming confused when this thing that looks vaguely like a range of cells on the sheet is malformed. Is this possible?

1

1 Answer 1

1

You can enter those declinations as durations of elapsed hours and format them to show a leading sign by choosing Format > Number > Custom number format >
"+"[h]":"mm":"ss;""[h]":"mm":"ss;"±"[h]":"mm":"ss.

The results look like this:

displayed value how to enter the value
-45:26:04 -45:26:04
-1:34:53 -1:34:53
-1:03:02 -1:03:02
-0:31:51 -0:31:51
±0:00:00 0:00:00
+0:31:21 0:31:21
+1:02:42 1:02:42
+1:34:03 1:34:03
+1:25:24 1:25:24
+91:25:24 91:25:24

Format the entry column as Format > Number > Duration. Note that the durations need to be entered without a leading + or ±.

Alternatively, enter numbers in a decimal format, and format them like this:

"+"0.00":"00;"-"0.00":"00;"±"0.00":"00

The results look like this:

displayed value how to enter the value
-45.26:04 -45.2604
-1.26:04 -1.2604
-0.94:53 -0.9453
-0.63:02 -0.6302
-0.31:51 -0.3151
±0.00:00 0
+0.31:21 0.3121
+0.62:42 0.6242
+0.93:63 0.9363
+91.25:24 91.2524

Note that in this scenario the numbers need to be entered without a leading + or ± and without colons :.

See Number format patterns.

2
  • It would be ideal for other cells' formulae if I could keep these in a time-like format (really, duration-like, since the "hours" analog element goes up to 90). Can this method be modified for that purpose?
    – Izzy
    Feb 23, 2023 at 0:04
  • 1
    Edited the answer. Feb 23, 2023 at 11:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.