[Year 12 Its] off topic (a little) sharing data between databases and spreadsheets

[Michael Walker]

>>> Rob Attrill<attrill.robert.j at edumail.vic.gov.au> 05/16/05 02:34pm >>>
>Sort of off topic but this seems the most likely place to get an answer.

>Why would an excel  spreadsheet which receives data in a .txt file from a database correctly import data such as 11B, 11C, 12B or 12C, but stubbornly receive 11A as .485333333 and 12A as 0? It is clearly importing it in a numeric form despite all attempts to format the receiving cells as text. Obviously, I can find plenty of "can do" fixes (using an apostraphe to precede the data, etc), but the issue itself is bugging me.

Hi Rob,

Looks like the data is importing as numerical time. For example 11AM calculates to 0.4583333333333 and 12AM is 0. Excel calculates date and time as number of days from a reference date (1/1/04 I think) and time as a fraction of a date (hence 11am being 11/24 or 0.458333333...). No idea how this is affected by your importing but it seems that for some reason Excel is ignoring your desire for the column to be text and converting the time to a number.