0

Im using Excel 2010 I am having trouble extracting the correct values from a RA and DEC values. I want to extract each value from the RA and DEC and place the deg, min, sec in separate columns.

Essentially just rewrite the values in separate columns. I cant do this individually as i have over 3500 items to convert.

Eg.

column A .....| B ...| C .| D

19:24:59.38 | 19 | 24 | 59.38

Here is what i have at the moment. The RA appears as '7:24:59 PM' in the text bar formatted as time "[h]:mm:ss.s" this appears as 19:24:59.59 in the cell A1.

to get deg:B1 = TEXT(TRUNC(A1,2)*24,"00") --> this produces 19 - correct

to get min:C1 = MID((TRUNC(A1,5)*24-B1)*60,1,2) --> this produces 24 - correct

to get sec:D1 = MID((((TRUNC(A1,5)*24-B1)*60)-C1)*60,1,5) --> this produces 58.46 - wrong. This should be 59.59 (decimal form) or preferably 59.38 (decimal degrees)

Can anyone tell me how to extract the correct values?

Thanks in advance.

Dmitry Pavliv
  • 35,333
  • 13
  • 79
  • 80
theotheraussie
  • 495
  • 1
  • 4
  • 14

1 Answers1

0

You should use [h]:mm:ss.00 for the format - then it will display the right value.

For B1 you can use =HOUR(A1) and for C1 you can use =MINUTE(A1) but for D1 you cannot use =SECOND(A1) because it truncates the decimal points so use =MOD(A1*86400,60) instead.

Perhaps a screenshot might help?

Jerry Jeremiah
  • 9,045
  • 2
  • 23
  • 32
  • I did as you suggest, the formatting you suggest just adds 00 after the decimal point making 59.59, 59.00. – theotheraussie Mar 19 '14 at 22:28
  • Also the MOD() calculations for the second seems to truncate the decimal point just like the SECOND() calculation. – theotheraussie Mar 19 '14 at 22:38
  • I have Excel 2010 and I made a spreadsheet with the formulas before I posted the answer. I don't see how your results would be different from mine but it really did work on my machine. – Jerry Jeremiah Mar 23 '14 at 07:14