Auto increment weekday and date


I have a spreadsheet where each cell has the following format

Weekday (linebreak)

For example:


Next cell:


I want to automatically display the whole year that way, so I am looking for a way to automatically fill/increment all cells in that way. When I do it in the regular way by dragging downward, it doesn’t work.

  1. Put 2019-01-01 into A1.
  2. Put 2019-01-01 into A2.
  3. Set cell format of A1 to NNN.
  4. Set cell format of A2 to M/D/YYYY.
  5. Select 2 cells: A1:A2.
  6. Using small square in the bottom right corner of selection rectangle, drag the selection down as far as you need, and check the result.

Another way, using two-lined cells:


This formula is intended for row 1. If the first calendar row is not 1, then the -1 part should be corrected accordingly.


I modified the formula a little bit with a cell where i can change the year as i have more sheets with different years… Can you help me out how can i maximise the date at the selected year’s december 31 and otherwise make the cell empty ("")? I need this because of the leap year day.