0

This question already has an answer here:

I have location points collected from a garmin device stored in an excel sheet in Degree Minutes format --- W00208.172,N1046.977.

I am looking for an Excel formula to convert either to Decimal Degrees or Degrees Minutes seconds Format ?

marked as duplicate by whuber Oct 3 '13 at 13:44

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

  • Are you looking for an Excel formula that will do this for you? – Paul Oct 3 '13 at 13:25
  • N1046.977' - is this a formatting issue? Is this N 10 degrees 46.977 seconds? Past that see John's answer below. – Russell at ISC Oct 3 '13 at 13:42
  • @Paul yes i am actually looking for an excel formula as i have almost 1000 cells like this – user2114562 Oct 4 '13 at 5:01
  • @user2114562: Have you looked at the formula given in answers to this, and the linked question? Have you been able to convert that to an Excel forumla? Where are you still stuck? – Devdatta Tengshe Oct 4 '13 at 5:41
  • 1
    It looks somewhat like the NMEA-0183 format (DDMM.MMM for lat, DDDMM.MMM for lon - just the hemisphere that normally comes after in GGA is before). So it isn't quite the same as DD to DMS. – BradHards Oct 4 '13 at 8:06
3

There are 60 minutes in a degree and 3600 seconds in a degree. So, for example, if I have a value of 98 deg 45 ' 56'' that would be equivalent to 98 + 45 / 60 + 56 / 3600 = 98.76556 deg. I hope that helps. Here's some robust pseudocode to handle all cases, including negative values:

double getDecimalDeg(int deg, int min, int sec) {
    if (deg >= 0) {
      return deg + min / 60 + sec / 3600
    } else {
      return deg - min / 60 - sec / 3600
    }
}
  • 1
    That's correct, but where such recipes often founder is with negative values. If you tried to convert -98 deg 45' 56" by naively applying the same pattern to compute (-98) + 45/60 + 56/3600 you would be very wrong. – whuber Oct 3 '13 at 13:45
  • 1
    @whuber Excellent point! I've edited my answer to reflect the rather common case of negative values. – user21951 Oct 3 '13 at 13:50
  • @whuber the coordinates are in DM not DD format – user2114562 Oct 4 '13 at 5:15
  • @user2114562 That only makes applying any conversion formula even simpler, because you can just leave out the seconds (or set them to zero). – whuber Oct 4 '13 at 14:40

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