formatted numbers displayed as #####

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

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 #####.

What actual format did they try to use?

--
HTH

Sandy
[email protected]
Replace@mailinator with @tiscali.co.uk


"NinaGrewalOff" <[email protected]>
wrote in message
news:[email protected]...
>
> 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...o&userid=27297
> View this thread: http://www.excelforum.com/showthread...hreadid=467972
>

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]...
>
> 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...o&userid=27297
> View this thread: http://www.excelforum.com/showthread...hreadid=467972
>

Thanks for the Help. To SandyMan: From what I gather they used the format cells, number and than date and than probably the first one (they are gone from office so I need to call them tomorrow). I'm hoping that they are showing up as ##### through their own incompetence 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 my 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. I went to Tools>Options>View and clicked on zero values. Now the problem is that this only works for 0's that stand on their own, not 0's that are part of a string of numbers. Also, this document has already been created and the 0's are probably not even on there. Is there a function that I could create that says basically if digits=5 input 0 at the front of digits for lines d2-d4600. Which is assuming that I can allow zero's to show up at the front of numerical strings (which I assume must be possible). Thanks for the Help, John Penner

> 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:

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

--
HTH

Sandy
[email protected]
Replace@mailinator with @tiscali.co.uk


"NinaGrewalOff" <[email protected]>
wrote in message
news:[email protected]...
>
> Thanks for the Help. To SandyMan: From what I gather they used the
> format cells, number and than date and than probably the first one
> (they are gone from office so I need to call them tomorrow). I'm
> hoping that they are showing up as ##### through their own incompetence
> 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 my
> 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. I went to
> Tools>Options>View and clicked on zero values. Now the problem is that
> this only works for 0's that stand on their own, not 0's that are part
> of a string of numbers. Also, this document has already been created
> and the 0's are probably not even on there. Is there a function that I
> could create that says basically if digits=5 input 0 at the front of
> digits for lines d2-d4600. Which is assuming that I can allow zero's
> to show up at the front of numerical strings (which I assume must be
> possible). Thanks for the Help, John Penner
>
>
> --
> NinaGrewalOff
> ------------------------------------------------------------------------
> NinaGrewalOff's Profile:
> http://www.excelforum.com/member.php...o&userid=27297
> View this thread: http://www.excelforum.com/showthread...hreadid=467972
>

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 |:>)

"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...o&userid=27297
> View this thread: http://www.excelforum.com/showthread...hreadid=467972
>
>

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" <[email protected]> wrote in message
news:[email protected]...
> 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 |:>)
>
> "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...o&userid=27297
>> View this thread:
>> http://www.excelforum.com/showthread...hreadid=467972
>>
>>

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 |:>)

"George Nicholson" wrote:

> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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 |:>)
> >
> > "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...o&userid=27297
> >> View this thread:
> >> http://www.excelforum.com/showthread...hreadid=467972
> >>
> >>
>
>
>