How do you convert a date in one cell to a different format

[clintsmyth]

I have a date in one excel sheet that I would like to link to another cell in another sheet. However, in the other sheet, I would like it to be displayed in a different format.

For example:

I would like cell B4 in sheet #1 to display the date located in cell B2 of sheet #2 (11.05.05) in the following format: November 05, 2005.

Could someone please offer a solution?

Thanks in advance!

Clint

 

[Datsmart]

Right Click the cell and choose Cell Format.
Click Custom and input MMMM DD, YYYY

 

[barry houdini]

just use in sheet1 B4

=sheet2!B2

and custom format cell as

mmmm dd, yyyy

OR use this formula

=TEXT(sheet2!B2,"mmmm dd, yyyy")

The first option leaves the data as a date but changes the format, the second changes the format to text

 

[clintsmyth]

Thanks for both of your replies. I had already tried the first option you both gave me without success and the second only returned the same date format: 11.25.05

Any ideas why it seems to be stuck?

 

[HalfAce]

Hi Clint,
How is your date in sheet2 being entered?
It appears it is not a true date but rather actually text that's made to look like a date.
If you have the option to enter the date in sheet2 like this:
11/05/05 you can then custom format it as "mm.dd.yy" so it'll look the same as it does now, but will be recognized as a date so you can work with it in sheet1.

 

[clintsmyth]

You're right on.

It didn't like the 11.25.05 date I was inputting into sheet 2. When I entered it into the format you recommended, everything worked!

Thanks for your help!

Clitn