Excel - format cell as text

[Katy44]

I have a load of dates that have come from a database in the format: 09/07/1936 00:00:00
I pasted these values only into Excel, and then I want to use them in exactly that format in another cell. If I just refer to the cell, it changes the contents to something else. I can get round this by adding a ' to the beginning of each cell, but why doesn't formatting the cells as text do the same job? Any help would be appreciated.

 

[jdemmi]

Can't you format the cells as "DATE" and the choose your format?

Can you explain what you mean by refer? (Are you using the date in a fomula in another cell?)

Also what version of Excel (Office)?

 

[01Patrik]

I pasted these values only into Excel, and then I want to use them in exactly that format in another cell.

Try format the cells as text first, then paste-special: values. This will keep the "text" format, and therefore not mess with your dates.


// Patrik

 

[01Patrik]

Of course, you can also set the target cells format to special:

DD/MM/YYYY" "hh":"mm":"ss

// Patrik

 

[SkipVought]

Katty,

Date/Time values are JUST NUMBERS, like right now is 38217.40308, which can be FORMATTED to DISPLAY in any number of ways.

[red]DO NOT, DO NOT, DO NOT change your Date/Time Values to STRINGS by using the apostrophy!!!!![/red]

That would be a BIG mistake. Learn how to MANIPULATE Date/Time values to your advantage.

Skip,

_{[red]Be advised:[/red]
Alcohol and Calculus do not mix!
If you drink, don't derive!}

 

[Katy44]

Thank you all for your replies
I have the string '07/10/2003' say, in a cell which is formatted as a date.

I want to use the string '07/10/2003' in another cell by referring to that first cell. What is the best way to go about it?

 

[Katy44]

In answer to all your questions (as my last post didn't add much!)

It is Excel 2000
The cells are formatted as dates
I then wish to refer to each cell in a text cell, but have it input as text in the same format.
I have tried copy - paste values only, but as this is a formatting issue it didn't have much effect.

 

[SkipVought]

If you have TEXT in A1, to create a REAL DATE
[tt]
=Date(Right(A1,4), Left(A1, 2), Mid(A1, 4, 2))
[/tt]


Skip,

_{[red]Be advised:[/red]
Alcohol and Calculus do not mix!
If you drink, don't derive!}