Formatting Dates

  • Thread starter Thread starter Sherry
  • Start date Start date
S

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
 
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!
 
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!
 
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
 
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.
 
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.
 
Debra Dalgleish said:
Another way to convert the dates to numbers, so you can format them, is
to use Text to Columns:
Click to expand...

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

Best wishes Harald
 
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!


.
Click to expand...
 
Back
Top