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

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

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

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?

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.

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 .

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.

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

I found a way.

cell A1: 1/1/2006

My solution is this:

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

then, paste special-values.

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