Formatting Dates

[Sherry]

I import a text file with date information coming in at
19990412 (yyyy/mm/dd). I would then like to format the
column to display the field as (mm/dd/yyyy) but when I
format the column my date information turns to pounds
signs ####.

Any help you can give me in this matter would be greately
appreciated.

Thanks
Sherry

 

[Domenic]

Hi Sherry,

I think that's because Excel is recognizing them as numbers and not
dates.

Try,

1) Select the column containing the numbers (dates)
2) Select Data > Text to Columns
3) Next
4) Next
5) Choose Date as your Data Format and select YDM
6) Click OK/Finish

Your numbers should now be recognized as dates.

Hope this helps!

 

[Guest]

It sounds like your column is not wide enough to
accomodate the new date format. If the font size is
larger or the period separators ("/") are added to the
format, you need to reduce the font size or increase the
column width.
Hope that helps!

 

[Harald Staff]

Hi Sherry

19990412 is NOT a date, it's simply a very very big number. You can't format
it to be its "lookalike date", formatting changes display, not real content.
The numeric value for april 12th 1999 is 36262 (= number of days since new
year to 1900), the date value of almost 20 million is way into the future
and represented by ###s.

With the very big number in cell A1, you can convert to the date with this
formula:
=DATE(INT(A1/10000),MOD(INT(A1/100),100),MOD(A1,100))
or with a similar macro.

HTH. Best wishes Harald

 

[Guest]

I'm not actually putting in the ("/") because the file
gets imported in just as 19990412 but I want to add the
("/") but when I do that is when I get the ####. I have
expanded the column width out to accomodate the size
because that was the first thing I thought of.

I do appreciate your help.

 

[Debra Dalgleish]

Another way to convert the dates to numbers, so you can format them, is
to use Text to Columns:

1. Select the column with dates
2. Choose Data>Text to Columns
3. Click Next, click Next
4. In step 3, choose MDY from the Date dropdown.
5. Click Finish.

 

[Harald Staff]

Another way to convert the dates to numbers, so you can format them, is
to use Text to Columns:

DOH!!!
Keep forgetting that one.
Probably a far better (and less educational) solution to this.

Best wishes Harald

 

[Guest]

Thank you for the reply. This fixed my problem.

-----Original Message-----
Hi Sherry,

I think that's because Excel is recognizing them as numbers and not
dates.

Try,

1) Select the column containing the numbers (dates)
2) Select Data > Text to Columns
3) Next
4) Next
5) Choose Date as your Data Format and select YDM
6) Click OK/Finish

Your numbers should now be recognized as dates.

Hope this helps!


.

 

[Debra Dalgleish]

Whatever its other benefits, it requires less typing than a formula!

 

[Domenic]

You're welcome, Sherry!

Thank you for the reply. This fixed my problem.