formatted numbers displayed as #####

  • Thread starter Thread starter NinaGrewalOff
  • Start date Start date
N

NinaGrewalOff

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 al
displayed as one long string of numbers, i.e. like 021105. So the
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 number
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 a
#####. Also while doing my own testing I found that even if they d
get the numbers shown they will probably get nonsensical dates. Fo
example, when I format using date (also did some custom formatting fo
the date but it does the same thing) my 21105 (as Excel deletes th
front 0) turns into 12/10/57, when it should show up as 02/11/05.
thought this might be a problem with the 0 not showing up so I di
121105 but this shows up as 28/07/2231. I have very little experienc
with Excel, yet because I know the most about computers amongst the tw
offices they want me to figure everything out. Any help would b
appreciated, thanks

John Penne
 
The ##### means the column is not wide enough to accommodate all the
numbers.
Try widening the column to 50 to see if the numbers appear. If the
numbers appear you can tell how wide the column should be.

Ralphael, the OLD one
 
Looks like I put my keyboard in motion before engaging brain.
Using your guidelines I did some experimenting and came up with the
same results as you did.
Really strange...

Ralph, the OLD one
 
John,

Dates in XL are a countof the number of days since 1 January 1900 so day
number 21,105 is 12 October 1957, that is why you get that date.

When you say
.........................................................................................
So they
went to format them into dates and they all ended up going into #####.
Click to expand...

What actual format did they try to use?

--
HTH

Sandy
(e-mail address removed)
Replace@mailinator with @tiscali.co.uk


"NinaGrewalOff" <[email protected]>
wrote in message
 
Use the Date function and string parsing to convert your text values into
the numerical value required by excel to display a "proper" date:
=DATE("20" & RIGHT(G4,2),MID(G4,3,2),LEFT(G4,2))
The Date function arguments are Date(Year, Month, Day). I assume from your
post that your text values are currently in ddmmyy format. If I'm wrong,
just swap them around. (I'm also assuming *all* text values are 6 characters
long)

021105 will convert to 38658 (the number of days since 01/01/1900) and
display (in dd/mm/yy format, which your post indicates you are using) as
02/11/05.

HTH,
--
George Nicholson

Remove 'Junk' from return address.


"NinaGrewalOff" <[email protected]>
wrote in message
news:[email protected]...
 
Thanks for the Help. To SandyMan: From what I gather they used th
format cells, number and than date and than probably the first on
(they are gone from office so I need to call them tomorrow). I'
hoping that they are showing up as ##### through their own incompetenc
and they just didn't follow my instructions (I guess I'll see tomorrow)
In regards to George: Thanks for the help it looks great on m
computer and should work. Now the problem is that the numbers ar
probably not all 6 characters long as any of the first line that i
under 10 will have the 0 in front which dissapears. I went t
Tools>Options>View and clicked on zero values. Now the problem is tha
this only works for 0's that stand on their own, not 0's that are par
of a string of numbers. Also, this document has already been create
and the 0's are probably not even on there. Is there a function that
could create that says basically if digits=5 input 0 at the front o
digits for lines d2-d4600. Which is assuming that I can allow zero'
to show up at the front of numerical strings (which I assume must b
possible). Thanks for the Help, John Penne
 
computer and should work. Now the problem is that the numbers are
probably not all 6 characters long as any of the first line that is
under 10 will have the 0 in front which dissapears. Try:
Click to expand...

=DATE(2000+RIGHT(A1,2),MID(A1,LEN(A1)-3,2),LEFT(A1,1+(LEN(A1)=6)))

--
HTH

Sandy
(e-mail address removed)
Replace@mailinator with @tiscali.co.uk


"NinaGrewalOff" <[email protected]>
wrote in message
 
Sorry to butt 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 |:>)
 
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.
 
Hi George-

Quite a valid observation with _no_ dispute at all. I was simply picking up
on the point made in the op;

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.

which gave me the impression that wherever the original data was coming
from, the attemp to solve the problem was being based on typing the content
without the delimiters.

I find that many newcomers to XL with experience in other predesigned apps
tend to think of cell formatting as the equivalent of an Input Mask.

Regards |:>)
 
Back
Top