1

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
1

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.

2
  • Converting dates to strings by using hacks is usually a bad idea, prone to errors, and the need to convert them back to dates in order to do date arithmetics is also error prone. I would suggest OP stay away from this. Sep 3 '17 at 0:33
  • 1
    pnuts: Answer Accepted. The 'Locale' thing changed to United Kingdom indeed solved almost all of my problems. Thank you very much. Sep 3 '17 at 14:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.