Custom Date Format using META Instruction

pizzaboy

Board Regular
Joined
Mar 23, 2015
Messages
56
Office Version
  1. 2019
Platform
  1. Windows
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
Code:
[< 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:
Code:
[<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>
 
Last edited:

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
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).
 
Last edited:
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0
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! :rofl:


Thanks Rick
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
Thanks all!

Yeah, I was having problems posting "<" Date to boards.
 
Upvote 0
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
without the space.

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

Forum statistics

Threads
1,224,823
Messages
6,181,178
Members
453,021
Latest member
Justyna P

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top