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

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

Replace@mailinator with @tiscali.co.uk


"NinaGrewalOff"
wrote in message
news:NinaGrewalOff.1vezek_1126811286.0877@excelfor um-nospam.com...

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"
wrote in message
news:NinaGrewalOff.1vezek_1126811286.0877@excelfor um-nospam.com...

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

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

Replace@mailinator with @tiscali.co.uk


"NinaGrewalOff"
wrote in message
news:NinaGrewalOff.1vf7qf_1126821975.7418@excelfor um-nospam.com...

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

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

"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