How do I validate latitude/longitude in terms of ddmmss

[Guest]

I have a form where I'd like users to enter their latitude and longitude in
the form: +/-DDMMSS (I'm trying to be user friendly). I also need to
validate that they have entered the data in the correct format (i..e, where
for longitude degrees can vary from +180 to -180, but that the corresponding
minutes can only vary from 00 to 59). I'm using Excel 2003, but for various
reasons, cannot use XML and check against a schema. Is there a simple custom
validation I can perform, or am I going to have to 1) break the data up into
different cells and check each individually or 2) convert to radians to
perform the check?

 

[Guest]

Check out this link...

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

 

[Dave O]

Do you have the latitude (pun intended) to use the format DD.DDDDDDDDD?
That is to say, express the location in terms of degrees only, with
decimal points to define accuracy instead of minutes and seconds? This
would preclude the need for data validation, altho it may entail some
conversion. It's been my preference to use decimal fractions of
degrees instead of minutes/seconds, since high degree of accuracy would
require decimal fractions of seconds.

 

[Guest]

Well, my problem is that my customers prefer the DDMMSS format, and I am
starting with a set of data that is already in that format. I currently have
the format for the cells set to custom 000000 for latitude and 0000000 for
longitude. I'm not adverse to creating a new column and converting and then
validating the data (I think). My problem would then be how to convert to
decimal or radians from my current format since I do not have a delimiter.

I tried using the [h]:mm:ss format suggested on the website given by the
other responder (even though I may have southern latitudes), but when I enter
010101 I get 242424:00:00. So even if I divide by 24, I still get
010101:00:00.

I know there is probably a simple solution, but I haven't got my head around
it yet.

Thanks,

Tricia