Auto-fill date function changes the YEAR and not the day

Question

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.

Answer 1

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).

Answer 2

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.

Answer 3

• you can use ARRAYFORMULA instead of dragging it

  =ARRAYFORMULA(TO_DATE(TEXT(ROW(INDIRECT("A"&DATEVALUE("2019-1-22")&
                                         ":A"&DATEVALUE("2019-1-29"))), "dd/mm/yyyy")))

  

Answer 4

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

Answer 5

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]