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.

Your post gave me an idea...not sure how to convice Quickbooks to pre-format
the data...so I opened a spreadsheet and formated the questionable column for
a text entry. Then I copied the data (Control-C) in QB and pasted (control-V)
in Excel. Looks like it worked....its a cheesy fix to what ought to be a
simple problem...but I think it may solve my problem.

Thanks for the inspiration!

"Dave O" wrote:

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.

I've encountered the same type of problem before.

Quickbooks is notorious for being hard to integrate with Excel. Excel is
notorious for treating data in dumb ways not intended by intelligent users.
--
Brevity is the soul of wit.


"Gary C" wrote:

Your post gave me an idea...not sure how to convice Quickbooks to pre-format
the data...so I opened a spreadsheet and formated the questionable column for
a text entry. Then I copied the data (Control-C) in QB and pasted (control-V)
in Excel. Looks like it worked....its a cheesy fix to what ought to be a
simple problem...but I think it may solve my problem.

Thanks for the inspiration!

"Dave O" wrote:

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.