Multiple Cell Formats

[Morris.C]

Is it possible for a column to have more than one format, but for those
formats to be similar.
Eg.
I would like to have a multi-format date column. The format can either be
Month-Year (Jan-99) or Year only (1999). (I would even like to have
something like Jan/Feb-99, but I can live without that.)

Any help would be appreciated.

Thanks.

 

[Roger Govier]

Hi

With true Excel dates in column A e.g. 01 Feb 07, in column B

=A1 Format>Cells>Number>Custom> mmm-yy will give Feb-07
or =TEXT(A1,"mmm-yy")
=A1 Format>Cells>Number>Custom> yyyy will give 2007
or =TEXT(A1,"yyyy")
=TEXT(DATE(YEAR(A1),MONTH(A1)-(DAY(A1)<15),1),
"mmm")&"/"&TEXT(DATE(YEAR(A1),
MONTH(A1)+(DAY(A1)>=15),1),"mmm yy")

will give Jan/Feb 07 because the date is before the 15th
15 Feb 07 would give Feb/Mar 07

Change the value 15 in the formula to make the break between months
occur where you want.

Note that where the break is Dec/Jan it will give the Year which belongs
to Jan, not the year belonging to December

 

[Morris.C]

What do you mean by "true Excel dates"?
If my cell shows Jan-93, when I double-click on it, it changes to
01/01/1993!

 

[Roger Govier]

Hi

Yes, then that is a true Excel date as opposed to Text saying "Jan-93"