Custom Date Formats in Google Sheets – How to Display Custom Month Abbreviations

google sheets

In Google Spreadsheets I don't like the built-in abbreviations of the month names in date formats. I especially hate the fact that abbreviations for month names doesn't have a fixed length. I think the use of a period in those abbreviations is ugly, useless and unnecessary. 

1-ene.-2017
1-febr.-2017
1-mzo.-2017
1-abr.-2017
1-my.-2017
1-jun.-2017
1-jul.-2017
1-ag.-2017
1-set.-2017
1-oct.-2017
1-nov.-2017
1-dic.-2017

AFTER ANSWER ACCEPTED:

1-Jan-2017
1-Feb-2017
1-Mar-2017
1-Apr-2017
1-May-2017
1-Jun-2017
1-Jul-2017
1-Aug-2017
1-Sep-2017
1-Oct-2017
1-Nov-2017
1-Dec-2017

Best Answer

These, I am guessing are representations of a date index (numbers formatted as dates) and to preserve that flexibility I suggest you change your Locale in Spreadsheet settings... (say to United Kingdom) in File. This though might not be convenient as it may also switch the decimal separator from , to ..

A suitable format then might be:

dd-mmm-yyyy

Other than something of that kind your only choice is to convert from Number format (Date) to Text format with string handling. So extract the month number, say with:

=month(A1)

look up its appropriate name/abbreviation (create a lookup table mapping numbers to whatever text suits you) and then concatenate it with day (1st) and year (2017), something like:

="01-"&VLOOKUP(month(A1),Month_Table,2,0)&"-2017"

However for most processing of the dates you would then need to reverse those steps.

Related Topic