Date won't format

[Guest]

I am trying to get a date column to apply a format. For example the dates
are entered as 1/22/2007 and I need them to be 01/22/2007 but when I go into
dates and select that format it does not apply it. Does anyone know what I
can do? These are expiration dates that we need to import into our eblast
program for membership renewals.

 

[Guest]

Create a custom format for the date and enter the following:

mm/dd/yyyy

 

[Bob Umlas]

Cell is probably formatted as text. Re-format as Custom, enter mm/dd/yyyy
(or find the equiavalent in the Date category), then re-enter the cell.
(F2/Enter)

 

[David Biddulph]

Try Data/ Text to Columns/ Delimited/ Next/ Next/ Column Data Format: Date:
MDY/ Finish
Format your result appropriately.

 

[Guest]

Still no luck. I have tried to even copy the data into a new spreadsheet. I
have gone in and created the custom format and still nothing. There are 700
of the dates in this file and I don't have them in any other format. This
file is it. Is there possibly a format lock on the cell?

 

[Guest]

I have tried the custom field but it won't apply it. I have also tried some
of the standard date formats but they don't apply.

 

[John Wilson]

Try multiplying the date cell(s) by 1
If it's a date, it should get you a 5 digit number that you can format as
any date format you want.
If it doesn't, then it's not a 'date" in the cell(s).
Possibly a space (or other character) before or after??

John

 

[Guest]

Do I put that information into the custom field?

Try Data/ Text to Columns/ Delimited/ Next/ Next/ Column Data Format: Date:
MDY/ Finish
Format your result appropriately.

 

[Guest]

Can you tell me how to multiply it by 1? I double checked a few cells and
there doesn't seem to be a space before or after.

 

[John Wilson]

Let's say that you have "11/04/06" in cell "A1"
In B1, type A1*1 and see what you get.
If B1 returns a date or a 39025 then what was in A1 was a date.

 

[Guest]

I'm playing a bit and if I double click on the cell it accepts the format.
Will I have to do this to all 700 records times two columns?

 

[Tom Ogilvy]

Not if you manually do this menu selection sequence with the troublesome
column selected:

Data/ Text to Columns/ Delimited/ Next/ Next/ Column Data Format: Date:
MDY/ Finish

or put 1 in an empty cell, then select the cell and do Edit=>Copy

then select your troublesome values and do Edit=>Paste Special and select
Multiply and Values

Now format the column with the suggested custom date format.

 

[Guest]

After all this grief, try setting your regional settings to mm/dd/yyyy. I had
a similar problem with different computers on a network.
Lou