turns data into datevalue

[amk]

i have a set of latitude and longitude data. we will just talk about the latitude data. it is in a one-cell-wide column in the following style:

40:11:00
38:03:06
etc

there are hundreds of rows of this.

thats

degrees:minutes:seconds

i need to convert this to decimal degrees

that should not be hard, all i have to do is use the "text to columns" utility and break that single column into 3 columns then i will have the following:

column a column b column c
40 11 00

then i make a column d such that column d =(column a)+([column b]/60)+([column c]/3600) to get

column d
40.183333

for example.

However, and this is my problem, when i try to use the "text to columns" utility, Excel converts my values into dates, for example 40:11:00 becomes

column a column b column c
1/1/1900 4 11 12:00 PM

I understand what it is doing, it is seeing my originl data and assuming that i want it converted into date values.

when i try to reformat the cells prior to splitting it into 3 columns, (say reformat to "general" ) it just converts my number into a julian date (the above example becomes 1.6...)

i cant seem to figure out hoiw to tell excell to just take the numbers as i have entered them and STOP TURNING THEM INTO DATES.

Any suggestions?

please email me at [email protected]

thanks

Aaron King

[Domenic]

Assuming that Column A contains your values...

B1, copied down:

=SUM(MID(SUBSTITUTE(TEXT(A1,"[hh]:mm:ss"),":",""),{1,3,5},2)/{1,60,3600})

...confirmed with CONTROL+SHIFT+ENTER, not just ENTER. Then, select Column B > Edit > Copy > Edit > Paste Special > Values > Ok.

Hope this helps!

[mudraker]

Aaron

The only way I could figure out to overcome you problem is:-

I assumed your data starts in A1

in b1 enter this formula
=VALUE(LEFT(A1,2))*24+HOUR(A1)

In c1
=MINUTE(A1)

In D1
=SECOND(A1)

Copy formulas down

Format columns b, c, d to General

Copy columns B, C, D then Paste.Special > Text