Geographical coordinates

[Alex Plantema]

I need to enter geographical coordinates as degrees, minutes and seconds in cells.
The format that comes closest is the time format :mm:ss,
but Excel displays, unlike Open Office, negative values as a string of #'s.
I also tried :mm:ss;-:mm:ss but that doesn't make any difference.
Is any other format more suited for this?

 

[Pete_UK]

You can display negative times if you switch to the 1904 date system,
although there are problems if you then use that workbook with the
1900 date system. John Walkenbach has some notes here:

http://spreadsheetpage.com/index.php/tip/dealing_with_negative_time_values/

You might also like to check out Chip Pearson's site, for more things
on degrees, minutes and seconds:

http://www.cpearson.com/excel/latlong.aspx

Hope this helps.

Pete

 

[Alex Plantema]

Pete_UK schreef in

You can display negative times if you switch to the 1904 date system,
although there are problems if you then use that workbook with the
1900 date system. John Walkenbach has some notes here:

http://spreadsheetpage.com/index.php/tip/dealing_with_negative_time_values/

You might also like to check out Chip Pearson's site, for more things
on degrees, minutes and seconds:

http://www.cpearson.com/excel/latlong.aspx

Thanks. I tried 1904 as starting date and then negative values are accepted,
but I cannot enter them directly, e.g. these forms are not accepted as input:
-18:00:00
=-18:00:00
=0-18:00:00
=00:00:00-18:00:00

but I can e.g. enter 18:00:00 in A1 and enter =-A1 in A2.
Then I can copy A2 and paste the value using Paste special over itself.
Then both the cell and the formula bar show -18:00:00.
But with values of -24:00:00 and smaller the formula bar is different,
e.g. -26:00:00 is shown as -02-01-1904 02:00:00 in the formula bar.

 

[Pete_UK]

Thanks for feeding back, Alex.

I didn't know that you couldn't enter negative times directly, but
then I've never needed to.

Pete