Custom Formatting "Sept '18" - is this possible

[bearcub]

Is this possible to insert an apostrophe between the month and year (replacing the date with an apostrophe) using the custom format dialog box

For Example:

Is it possible to format Sept 1, 2018 in a cell to display Sept '18.


When I've done this in the past I've had to manually type this into the cell. I was wondering if there was a way of doing this using custom formatting so the underlying value isn't replaced with text.

Michaeo

 

[Rick Rothstein]

There is no cell format that would product a 4-character month name, only a 3-character one (the apostrophe would be easy). We can write a VBA event procedure that will do it, but first you need to show us all of the month abbreviations you want to use (I'm guessing you don't want things like Janu, Marc, Augu for example).

 

[bearcub]

Hi Rick,

My bad about the 4 month character, it is only 3 as you say.

(the apostrophe would be easy)

How would I include the apostrophe to create something like this "Sep ' 18".


I tried this a couple of different ways but I got an error message?

Would I use mmm ' yy?

What would the VB code look like, just curious?

thank you for your help,

Michael

 

[fhqwgads]

yeah, putting mmm 'yy in the custom format should work unless your cell doesnt contain a valid date and just has text of the date

 

[fhqwgads]

here's the macro recording of it so you know what the code looks like

Sub Macro3()
'
' Macro3 Macro
'

'
    Selection.NumberFormat = "mmm 'yy"
End Sub

 

[bearcub]

Thank you both for the update. I think when I applied the custom format I didn't have an actually date in the cell. I'll do it again to see if it works.

Michael