Re: formatted numbers displayed as #####

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

Given the problem as laid out (and for some of the reasons you site, or I'd
wonder why did it take so long for the question to come up), I assume the OP
has an imported set of data that he is trying to make usable for further
work, not data that was being input for the first time.

That said, preventing it from happening again may not be a) an issue or b)
possible.

--
George Nicholson

Remove 'Junk' from return address.


"CyberTaz" <CyberTaz@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:A91074A4-09B5-4432-8995-ADCF5E553406@xxxxxxxxxxxxxxxx
> Sorry to *** in, and certainly no disrespect for the expertise offered,
> but
> I'm not sure the point was clear for avoiding the problem continuing to
> happen once this occurence is corrected.
>
> When entering dates in Excel (typically, at least), the user _must also_
> type in the month/day/year delimiters regardless of the formatting applied
> to
> the cells, i.e.,
>
> 9/15/2005 or 9-15-2005 (the first 2 digits of the year are
> optional).
>
> Just typing 9152005 is a number which XL _tries_ to convert to a date if
> the
> cell is formatted for Date/Time as indicated in Sandy's first reply.
>
> Also, leading 0s for the month & day need not be typed... it is the
> formatting of the cells that determine whether they display.
>
> Regards |:>)
>
> "NinaGrewalOff" wrote:
>
>>
>> Another office phoned me about a problem they are having with excel.
>> They had a large list of dates about 4600 columns long that were all
>> displayed as one long string of numbers, i.e. like 021105. So they
>> went to format them into dates and they all ended up going into #####.
>> As I was looking through the help topics on Excel I found that numbers
>> will be displayed like this if the column is not big enough in width.
>> So I got them to do that but to no avail, they are still displayed as
>> #####. Also while doing my own testing I found that even if they do
>> get the numbers shown they will probably get nonsensical dates. For
>> example, when I format using date (also did some custom formatting for
>> the date but it does the same thing) my 21105 (as Excel deletes the
>> front 0) turns into 12/10/57, when it should show up as 02/11/05. I
>> thought this might be a problem with the 0 not showing up so I did
>> 121105 but this shows up as 28/07/2231. I have very little experience
>> with Excel, yet because I know the most about computers amongst the two
>> offices they want me to figure everything out. Any help would be
>> appreciated, thanks
>>
>> John Penner
>>
>>
>> --
>> NinaGrewalOff
>> ------------------------------------------------------------------------
>> NinaGrewalOff's Profile:
>> http://www.excelforum.com/member.php?action=getinfo&userid=27297
>> View this thread:
>> http://www.excelforum.com/showthread.php?threadid=467972
>>
>>


.

Quantcast