Combining text and date in one column

[Stacey]

I have a very old spreadsheet originally created in Macintosh Works that has
entries of either dates (mm/dd/yyyy), years (1986), or text in the cells. If
I format the column to text, then my dates turn into numbers. If I format it
to dates, then my numbers turn into incorrect dates. I can format and fix
the original information, but how can I format the column for future entries?

 

[Stacey]

Thank you for the info. I understand how Excel can show the data in a date
field if it's not a date and how it shows data in a text field if it is a
date. The problem is that the original information is from a database that I
had to copy and paste into an Excel file for Windows. I had 3 types of
information in the column. Actual text - which comes through the same
whether I format as date or text; an actual date, which comes through as a
number if formatted as text; and a year only, that comes through as a date if
formatted as date. In summary, my information is mixed. If I start a brand
new spreadsheet, format as text, then start typing, I get what I want. But
using the existing information doesn't work. Does that make sense?

 

[Stacey]

Well, that's what I was afraid of. I've tried copying and pasting and
pasting special, but to no avail. I guess I should just be happy that we
were able to get the file converted at all!! Thanks for your comment. Have
a great weekend.

 

[dranon]

I have a very old spreadsheet originally created in Macintosh Works that has
entries of either dates (mm/dd/yyyy), years (1986), or text in the cells. If
I format the column to text, then my dates turn into numbers. If I format it
to dates, then my numbers turn into incorrect dates. I can format and fix
the original information, but how can I format the column for future entries?

You can't. Or, better I should say, you can only provide a format
that is one or the other, not both. You have to pick one and then
manually change it to the other for any entries that need to be
changed.

 

[ebloch]

Why not insert blank columns and then use a macro with if functions using
"isnontext()", "istext()", and "isnum()" to copy the data to one of the new
columns.

Then format the columns as you want.

Eric

 

[Ron Coderre]

Select the range with years and dates

From the Excel Main Menu:
<format><cells><number tab>
....Category: Custom
....Type: [<20000]0000;[>=20000]mm/dd/yyyy;General
....Click: OK

With that custom number format
• Numbers less than 20000 display as years
• Number >=20000 display as dates

Examples:
2009 displays as 2009
03/27/09 displays as 03/27/2009
39899 displays as 03/27/2009

Is that something you can work with?

Regards,

Ron Coderre
Microsoft MVP (Excel)

 

[Rick Rothstein]

Assuming none of your dates are before May 18, 1927, the this Custom Format
should do what you want...

[<10000]General;mm/dd/yyyy

If you have dates prior to that, then assuming none of your dates will take
place in the year 2100 or later, and assuming none of your dates are before
September 30, 1905, then this Custom Format would do what you want...

[<2100]General;mm/dd/yyyy

 

[Stacey]

Wow, thanks everyone for the great ideas. Can't wait to get back to the
office and try them out!! Don't you just love spreadsheets?

Assuming none of your dates are before May 18, 1927, the this Custom Format
should do what you want...

[<10000]General;mm/dd/yyyy

If you have dates prior to that, then assuming none of your dates will take
place in the year 2100 or later, and assuming none of your dates are before
September 30, 1905, then this Custom Format would do what you want...

[<2100]General;mm/dd/yyyy

--
Rick (MVP - Excel)

I have a very old spreadsheet originally created in Macintosh Works that
has
entries of either dates (mm/dd/yyyy), years (1986), or text in the cells.
If
I format the column to text, then my dates turn into numbers. If I format
it
to dates, then my numbers turn into incorrect dates. I can format and fix
the original information, but how can I format the column for future
entries?