I am trying to import a table from Quickbooks. I save the excel file but
when I open the file one column of data is wrong. It is numerical data (ie
6000:6105) and I suspect that excel is performing a calculation on the data.

I tried to reformat the data as text but that does recreate the original
data. I have tried to turn off the auto-calculation on the options menu but
the menu change does not seem to stay in place when I open the file.

Is the colon an operator?

Excel may be interpreting the colon as a delimiter as in a time
display. What displays in the cell? You want to see 6000:6105, but
what does Excel show? I'm sure there's a workaround.

That particular cell shows 6101.75. Realize that is one example of 390
entries in that column.



"Dave O" wrote:

Excel may be interpreting the colon as a delimiter as in a time
display. What displays in the cell? You want to see 6000:6105, but
what does Excel show? I'm sure there's a workaround.

Excel is interpreting the 6000 as minutes and the 6105 as seconds, then
converting the seconds to minutes (6105 / 60 = 101.75) then adding the
minutes to arrive at 6101.75.

In the formula bar above the column headers, does Excel show 6000:6105,
or the converted 6101.75?

The foruula bar shows 6101.75

Here is another example. 2000:2012 is showing up as 2033.53. Does the
hours/minutes theory still hold up?

"Dave O" wrote:

Excel is interpreting the 6000 as minutes and the 6105 as seconds, then
converting the seconds to minutes (6105 / 60 = 101.75) then adding the
minutes to arrive at 6101.75.

In the formula bar above the column headers, does Excel show 6000:6105,
or the converted 6101.75?

Yes: 2000 + (2012/60)

This may be tricky, since I'm not a quickbooks user. Excel makes a lot
of assumptions about your data which can be as annoying as they are
helpful. The question comes down to: manipulate the data before it
arrives at Excel or after it arrives at Excel. I was concentrating on
an "after" solution, but the one I had in mind (reformat cells to show
their intended value) won't work because Excel makes the assumption and
displays the FUBARred data instead of the raw data.

The next choices a convince quickbooks to export that cell as a text
string instead of numbers with a colon in the middle, or convince Excel
to treat that column differently. I don't know Quickbooks, so I can't
offer a solution there. When you import to Excel, does your data open
in a new spreadsheet? If it opens in an old sprdsht you can format
that column as text, which might do the trick.