Custom Date Format using META Instruction

[pizzaboy]

If a date is over a year old, I would like the cell to show "yyyy", otherwise "mm/dd".


I've tried:
<date(year(today())-1,month(today()),day(today()))]yyyy;mm dd
<date(year(today())-1,month(today()),day(today()))]yyyy;mm dd
<date(year(today())-1,month(today()),day(today()))]yyyy;mm dd

[< DATE(YEAR(TODAY())-1,MONTH(TODAY()),DAY(TODAY()))]yyyy;mm/dd
<date(year(today())-1,month(today()),day(today()))]yyyy;mm dd
<date(year(today())-1,month(today()),day(today()))]yyyy;mm dd"

but Excel reverted the instruction back to [<0].




I then tried:

[<42931]yyyy;mm/dd

but it always return the "mm/dd" format. 42931 is DATE(YEAR(TODAY())-1,MONTH(TODAY()),DAY(TODAY())) converted to a number.




Is this even achievable?


Any help would be greatly appreciated.


Thanks.</date(year(today())-1,month(today()),day(today()))]yyyy;mm></date(year(today())-1,month(today()),day(today()))]yyyy;mm></date(year(today())-1,month(today()),day(today()))]yyyy;mm></date(year(today())-1,month(today()),day(today()))]yyyy;mm></date(year(today())-1,month(today()),day(today()))]yyyy;mm>

 

[Rick Rothstein]

Your first attempt won't work because I believe the value you compare to must be a constant. Your second attempt worked fine for me. As for how to handle it in general, I think you will need to Custom Format your cell with mm/dd and use Conditional Formatting to test for more than a year old and set the conditional format to yyyy (Conditional Formatting overrides Cell Formatting).

 

[mole999]

think you should be able to force to text

= text(cell,"yyyy") and text(cell,"mm/dd")

the cells format would have to be adjusted by VBA otherwise

 

[pizzaboy]

Thanks for the reply Rick.

Haven't used Conditional Formatting for display before (only Font and Cell Color), really intrigued how I would go about this?

 

[pizzaboy]

Its ok, resolved it!

Been using Excel (and the VBA side of it) for years, hadn't even noticed I could adjust the Display from Conditional Formatting till now!


Thanks Rick

 

[Rick Rothstein]

Thanks for the reply Rick.

Haven't used Conditional Formatting for display before (only Font and Cell Color), really intrigued how I would go about this?

Select the range, let's say C2:C9, with C2 as the active cell. Click the Conditional Formatting button, select "Use a formula to determine which cells to format" and put this formula in the formula field...

=C2<DATE(YEAR(TODAY())-1,MONTH(TODAY()),DAY(TODAY()))

then click the Format button, select the Number tab, select "Custom" from the list and put yyyy in the Type field, then OK your way back to the worksheet. Assuming you Cell Formatted these cells with mm/dd, put a date that is less than a year from today and the month/day will be displayed... put a date more than a year from today and just the year will be displayed.

 

[MARK858]

Just to help as Rick's formula was being affected by the board interpreting part of it as a HTML tag, the formula he posted is

=C2<DATE(YEAR(TODAY())-1,MONTH(TODAY()),DAY(TODAY()))

 

[Rick Rothstein]

Just to help as Rick's formula was being affected by the board interpreting part of it as a HTML tag, the formula he posted is

Thanks for noticing that (I keep forgetting about the "less than" sign being interpreted as the beginning of an HTML tag) and posting the complete formula.

 

[pizzaboy]

Thanks all!

Yeah, I was having problems posting "<" Date to boards.

 

[MARK858]

Thanks all!

Yeah, I was having problems posting "<" Date to boards.

Either put a space after the less than symbol and tell others to remove the space or replace the less than symbol with

& lt;

without the space.

Please note it happens when the less than symbol is followed by a letter.