Paste the value of the format into a new cell
 

Hi,

I have a cell that has a value of 1/1/2006, and the format displays it
as Jan '06.

I would like to paste the format of that cell into a new cell so that
the value of the new cell is Jan '06, not 1/1/2006.

Is this possible in Excel or VBA?

Excel 2003

thx,
Bodi

Paste the value of the format into a new cell
 

1. Use the format brush to copy it from the correct cell to the new
cell.
2. Use Formaty/Cell/number and pick the format you want. Jan'06 is a
standard date format.
3. Copy the Jan '06 cell into the new cell
4. There are probably more ways. ed

Paste the value of the format into a new cell
 

what?

I'm not following you here Ed.

Firstly, I'm not sure what the difference is between 1 and 2. It
sounds like you're, first, asking me to copy the format, then, second,
set the format manually (redundant), then, third, copy the cell.

But this is exactly my problem, it's copying the value instead of the
actual format.
I need the new cell's actual value to be Jan '06, not just the format
of the cell.

thx

Paste the value of the format into a new cell
 

Sorry, I gave you 3 ways to do it. I don't understand your specific
problem. You can copy format only with paste special or the format
brushj. If you copy the cell it copies the value AND the format. What
do you want, because your last two sentenses contradict each other?

Paste the value of the format into a new cell
 

cell1
value: 1/1/2006
format: jan '06

cell2
value: jan '06 (basically, cell1's format)
format: don't matter

I need to copy cell1 into cell2 with the above parameters.

Paste the value of the format into a new cell
 

I ithought I understood your first question but this last one is beyond
me. There are 3 ways to copy the format of one cell to anotherl
Using the "Format Brush", copy/past-special-format only, and copy the
cell and the format automatically goes along with it. The date in cell
2 will stay the same with the first and second method. What part of
this don't you get, or want to happen? ed .

Paste the value of the format into a new cell
 

I'm totally cool with your 3 methods, they just don't relate to my
question. Well, they relate fine, but they don't solve it.

I want the underlying (actual) value of the pasted cell to be the
formatted "value" of the copied cell.

lol, it's funny how communication breaks down online - not that our
species has it figured out offline either.

Paste the value of the format into a new cell
 

From your first post it *appears* that you want to FORMAT the new cell
the same way as the original cell , i.e. you want it to say "JAN'06"
instead of 1/1/2006, or 1/15/2006, and 2/24/2004 would appear as
FEB'04), This is a FORMATTING problem and has nothing to do with the
CONTENT or ACTUAL DATE in either cell. It didn't look like you were
trying to copy the content of the first cell to the second or you would
have posed your example "so that the value of the new cell is Jan'06
instead of 2/24/2004" ).

Virtually anything you could be attempting is possible in Excel. Go
back to your original post and try to tell us what you can't do.
ed

Paste the value of the format into a new cell
 

I found a way.

cell A1: 1/1/2006

My solution is this:

cell A2: = TEXT(A1,"mmm 'yy")

then, paste special-values.

Paste the value of the format into a new cell
 

I give up. Without your telling us your parameters I don't know what
you're trying to do. You can get the same result, and a lot easier, by
simply copying cell A-1 and pasting it into cell A-2. I did have to
construct a new format to get mmm'yy instead of mmm-yy but once
established it carries into every pasted or copied cell (was that your
problem?). Unlike your solution if A-1 changes to a different date, A-2
will not change (but you didn't say your criteria in this regard).
If you want the date to change in A2 when it changes in A1 simply put
the formul =A1 in cell A-2. You don't say what you are copying in
order to paste special values. If it is A-1 your copying you get a
number with paste special values. If it is A-2 you'r copying you might
as well have put =A2 in the new cell. ed