I'm trying to get Google Sheets to auto-fill dates in the beneath cells after I have manually filled one myself. The strange issue is that when I hold Command key and drag down, the cells auto-populate with dates but it is the YEAR that changes, and not the day or month.

I have formatted the column with the day/month/year date pattern. I have tried adding my own custom format to the list but it doesn't allow it. It seems the sheet does recognise it as a date, but isn't understanding that I want the day to increase, not the year.

  • 1
    Thanks :) I've tried formatting the cells both before and after inputting the data and the same thing occurs. I doubled-checked the sheet's locale was set to UK and it is. The date I'm keying in is 17.12.17. The only thing I can think is that the use of the fullstops is throwing it off. I'll try slashes and see if that works. – Adele halsall Dec 14 '17 at 23:07
  • You can find the answer here. – marikamitsos Feb 17 at 11:56

First thing: don't hold Command key.

Second: Make sure your computer have the date configured accordingly. If you want to use the date in the format DD/MM/YYYY, you have to set this in your computer.

In my case, if I enter the date in the "wrong" format, let's say: 05/30/2017 (MM/DD/YYYY), when I drag the square down, it updates the year.

  • Thanks for replying :) I've checked the date format on my macbook and it is the exact same format I'm trying to employ in Sheets (DD/MM/YY). – Adele halsall Dec 14 '17 at 23:09

Just use slash / as a separator even thought the format is set to dot . separator (date will appear as dd.mm.yyyy, but when you will double click to edit, you will see, that data inside is dd/mm/yyyy).


make sure your day and month abbreviation is only 3 char (ex Tue vs Tues or Jul vs July) and then select two columns, drag plus symbol across and it should populate your columns by increasing day, date. [Mon, Jul 1, 2019; Tue, Jul 2, 2019]

  • you can use ARRAYFORMULA instead of dragging it

                                           ":A"&DATEVALUE("2019-1-29"))), "dd/mm/yyyy")))



Make sure your spreadsheet is set to United Kingdom. File>Spreadsheet Settings>Locale

  • 1
    Why should it be set to United Kingdom and not to any other country? – Rubén Nov 21 '18 at 19:03

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.