将LAT LONG坐标从String转换为Elapsed Time值

I can retrieve values from our database at work that represent coordinates. I use SQL to retrieve the data and open the results in Excel. They're stored to appear as coordinates in the degrees format, e.g. DDMMSS for LAT and DDDMMSS for LONG.

我可以在代表坐标的工作中从我们的数据库中检索值。我使用SQL来检索数据并在Excel中打开结果。它们被存储为以度数格式出现的坐标，例如用于LAT的DDMMSS和用于LONG的DDDMMSS。

The excel sheet is created with two columns labeled LAT and LONG. However the values are formatted as General and so Excel treats them as string. e.g. 987654 for LAT and 9876543 for LONG

excel表创建了两列标记为LAT和LONG的列。但是，值的格式为General，因此Excel将它们视为字符串。例如LAT为987654，LONG为9876543

I need the data as time values to perform Great Circle Distance calculations as described here:

我需要数据作为时间值来执行Great Circle Distance计算，如下所述：

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

I manage to convert the string values to Time values by using:

我设法通过使用以下方法将字符串值转换为时间值：

=TIME(VALUE(MID(A1,1,2)),VALUE(MID(A1,3,2)),VALUE(MID(A1,5,2)))

However the HH values are not maintained.

但是，不保持HH值。

e.g. 544039 becomes 06:40:39

例如544039成为06:40:39

The 54 becomes an 06 because 54/24 = 2.25 and 0.25*24 = 6

54变为06，因为54/24 = 2.25和0.25 * 24 = 6

BUT I need to maintain it as 54 not 06.

但我需要保持54而不是06。

I believe Excel calls it "Elapsed Time" in the following format:

我相信Excel将其称为“Elapsed Time”，格式如下：

[hh]:mm:ss

How can I properly convert 544039 from a string value, to an Elapsed Time value; 54:40:39?

如何正确地将544039从字符串值转换为Elapsed Time值; 54:40:39？

Any help would be appreciated. I'm open to running VB scripts if needed.

任何帮助，将不胜感激。如果需要，我愿意运行VB脚本。

2 个解决方案

#1

I think your confusion is that with an incoming value of "544039", it can't be HHMMSS, but is actually DegreesMinutesSeconds. You would be better off converting the MinutesSeconds part of the input string to fractional degrees, and storing the resultant decimal angle, i.e. "544039" becomes 54.6775, which can be stored as a number. See this article on how to do the conversion.

我认为你的困惑是，传入的值为“544039”，它不能是HHMMSS，而实际上是DegreesMinutesSeconds。最好将输入字符串的MinutesSeconds部分转换为小数度，并且存储结果小数角度，即“544039”变为54.6775，可以存储为数字。请参阅有关如何进行转换的文章。

Also, Excel stores dates as decimals, so doing this conversion then formatting the results as elapsed time would have the effect you want. Try entering a time manually, then change its format to decimal and you will see what I mean.

此外，Excel将日期存储为小数，因此执行此转换然后将结果格式化为已用时间将具有您想要的效果。尝试手动输入时间，然后将其格式更改为十进制，您将看到我的意思。

#2

I found a great solution to convert it to the necessary number value here:

我发现了一个很好的解决方案，可以将它转换为必要的数值：

http://www.excelbanter.com/showthread.php?t=235046

A reply submitted by Bernard V Liengme, so credit goes to him!

Bernard V Liengme提交的答复，所以归功于他！

Here's the code:

这是代码：

=(INT(A1/10000)/24)+(MOD(INT(A1/100),100)/(24*60))+(MOD(A1,100)/(24*60*60))

Thanks to everyone who replied, it helped guide my further searches and taught me a few new things.

感谢所有回复的人，它帮助指导了我的进一步搜索，并教会了我一些新的东西。

Cheers.

干杯。

#1

#2