excel

User-defined date format with an exception


I want to apply a custom date format like this: "Jan. 2025, Feb. 2025, Mar. 2025" and so on. So the format looks like this: MMM. YY in Right click > Format cells > Custom. It works fine except for the month May. There is a dot at the end. Is there any way I can create an exception for the month May, so that it will not a dot at the end?


Solution

  • While number formats do support simple conditions, this one is complex (due to calling the MONTH function) and is done in the conditional formatting.

    1. Select your range.
    2. Apply the default number format MMM. YY on it.
    3. While still selected, Add a new Conditional Formatting rule:
      1. Select the last rule type (Use a formula to determine which cells to format)
      2. Enter the formula =MONTH(A1)=5 (Assuming A1 is the top left cell of your range, change the address accordingly)
      3. Click format in bottom right
      4. Go to the [Number] tab.
      5. Set the custom number format MMM YY.

    You could also do step 2 as a conditional format rather than the regular number formatting, but in that case, you will need to make sure the 2 rules are applied in the correct order or you tick the [Stop when true] checkbox (though if you do, it may interfere with other conditional formatting rules; I am not sure I would recommend you tick it just to solve that question of yours).