Skip to Content

How To Autofill Dates In Google Sheets Columns (By Days, Weeks, Months, & Years)

Posted on Last updated:

Do you want to autofill dates in Google Sheets?

Google Sheets is one of the best spreadsheet programs for organizing dates. It comes with the autofill feature to fill the dates in sequential order in a column.

Manually entering dates in tens or hundreds of cells is time-consuming.

Let us learn to automatically fill in the dates in Google Sheets in this article. It is a quick guide with lots of useful information. So, make sure to read it till the end.

Download the Example Google Sheet

Before we dive deep into the actual steps, make sure to download the following Google Sheet. It contains the spreadsheets with the datasets used to demonstrate the autofill feature in Google Sheets.

Click Here!

It will surely help you improve your skills to use the autofill feature through practice.

You can skip downloading the above file if you have your own Google Sheet ready with the required data.

Now, without any further ado, let’s get started.

How to Autofill Dates in Google Sheets (By Days)

It is pretty straightforward to fill the days in sequential order in Google Sheets.

We need to make use of the Fill Handle by Google Sheets.

The entire process is divided into two steps.

STEP #1: Enter your first date

We need to start by entering our first date in the spreadsheet.

  • Open a new Google Sheet by clicking here
  • Select the first cell
  • Type the date as “1/1/2023”
  • Press the “Enter” key

That’s it!

If you prefer to enter the date in another format, then you must make sure it is acceptable by Google Sheets or not before moving ahead in this article.

PRECAUTION – Check for Valid Dates

Dates can be represented in various formats. As human beings, it is pretty easy for us to interpret them. However, when it comes to Google Sheets, it is not the case.

Google Sheets supports a limited number of date formats. Consider the following example:

In the above image, the first three dates are in the correct format and acceptable by Google Sheets. On the other hand, the remaining two dates are invalid.

How to check the valid dates in Google Sheets

There are three ways to check if the date is valid or not.

METHOD #1: Check the alignment

Google Sheets automatically shift valid dates to the right side of the cell.

Here is an example:

The first three dates follow a valid date format and, hence, are shifted to the right side of the cell. The remaining two dates are invalid, so they are shifted to the left side of the cell.

Sidenote: Make sure to adjust the width of the cell if you are unable to check the alignment of the date.

METHOD #2 – Use the ISDATE Function

The ISDATE is a simple function in Google Sheets that returns the value TRUE for valid dates and FALSE for invalid dates.

It has the general syntax as follows:

=ISDATE(value)

The “value” argument needs to be replaced with the date you wish to validate. You can manually enter the date or select the cell reference that holds the date.

Here’s the output after using the ISDATE function:

As seen in the above image, the function has returned TRUE for the first three dates and FALSE for the remaining two days.

METHOD #3 – Use the DATEVALUE Function

The DATEVALUE function converts a valid date to an integer number. It has the following general syntax,

=DATEVALUE(date_string)

Here, the “date_string” argument needs to be replaced with the date that you wish to convert to a number. You can either manually enter the date or provide the cell reference containing the date.

Note that, unlike the ISDATE function, which returns TRUE and FALSE for valid and invalid dates, the DATEVALUE function will return an integer number for valid date and the “#VALUE!” error for invalid date.

Let us use the DATEVALUE function for the above dates.

You can see that the function has successfully converted the first three dates to integer numbers and displayed the error for invalid dates in the fourth and fifth cells.

STEP #2 – Use Fill Handle to Autofill Days

In this step, we will autofill the dates in sequential order. Similar to the previous step, it is really easy to follow.

  • Hover to the lower-rightmost corner of the cell
  • You will see a “+” icon will appear
  • Click on the Fill Handle
  • Drag it to the end of the table

Google Sheets will continue to fill the dates until you stop dragging the Fill Handle.

Note that if you have a table, as shown below, that contains the Date column, you can simply hover to the Fill Handle until you see the “+” icon and double-click on it to autofill the dates for that entire table.

How to Autofill Dates in Google Sheets (By Weeks)

The steps to autofill weeks in Google Sheets are similar to the ones discussed in the previous section.

Instead of entering a single date, you need to enter two dates for the respective weeks.

Let’s get started now,

STEP #1 – Enter the first two dates

We need to start by entering the first two dates in the spreadsheet.

  • Open a new Google Sheet by clicking here
  • Select the first cell
  • Type the date as “1/1/2023”
  • Press “Enter” to go to the next cell right below it
  • Type the date as “1/8/2023”
  • Press “Enter” key

Note that the date format (mm/dd/yyyy) used in the above steps is valid and acceptable by Google Sheets. In case you use a different date format, make sure to check if it is valid.

You can simply check the alignment of the date after entering it in the cell. If it is aligned to the right of the cell, then it means it is a valid date.

STEP #2 – Use Fill Handle to Autofill Weeks

This is the crucial step that allows us to autofill the weeks in sequential order.

  • Select the first two dates
  • Hover to the lower-rightmost corner of the second cell
  • You will notice a “+” icon appear over the Fill Handle
  • Click on that “+” icon
  • Drag it using your mouse till the end of the table

Google Sheets will instantly fill in the weeks, as shown above.

How to Autofill Dates in Google Sheets (By Months)

It also uses the same steps as discussed in the previous section.

Let’s begin,

STEP #1 – Enter the first two dates

We will start by entering the first two dates. Note that these two dates must be the first date of each month to avoid confusion in the future.

  • Open a new Google Sheet by clicking here
  • Select the first cell
  • Put date as “1/1/2023”
  • Press “Enter” and you will be moved to the next cell right below the first cell
  • Put the date as “2/1/2023”
  • Press the “Enter” key

The date format (mm/dd/yyyy) used in the above steps is valid and acceptable by Google Sheets. If you plan to enter any different date format, make sure it is valid; otherwise, the autofill feature won’t work.

To identify if the date is valid, simply check the alignment of the date with respect to the cell holding it. If the date is aligned to the right, it means it is valid. All dates that are aligned to the left of the cell by default are invalid.

STEP #2 – Use Fill Handle to Autofill Months

In this step, we will use the Fill Handle visible at the lower-rightmost corner of the cell to autofill months in Google Sheets.

  • Select the first two dates
  • Hover over the Fill Handle visible at the lower-rightmost corner of the second cell
  • A “+” icon will appear
  • Click on that “+” icon
  • Drag it till the end of the table

All the months will be filled in a sequential order, as shown above.

How to Autofill Dates in Google Sheets (By Years)

To autofill years in the Google Sheets, you need to follow the same steps discussed in the above sections.

STEP #1 – Enter the first two dates

We need to start by entering the first two dates for the respective years. Make sure to enter the start date for each year to avoid confusion in the future.

  • Open a new Google Sheet by clicking here
  • Choose the first cell
  • Enter the first date as “1/1/2023”
  • Press “Enter” to move to the next cell right below the first date
  • Enter the second date as “1/1/2024”
  • Press “Enter” on your keyboard

Make sure to check the date format. The mm/dd/yyyy format used above is valid and acceptable by Google Sheets. If you choose to use another date format, ensure to check the alignment of the entered date with respect to the cell.

All dates aligned to the right of the cell are valid. If the date entered by you is shifted to the left automatically, then it means it is not a valid date.

STEP #2 – Use Fill Handle to Autofill Years

Again, let us use the Fill Handle to autofill years in Google Sheets.

  • Select the first two dates
  • Hover to the second cell, and you will see that a “+” icon appears over the Fill Handle
  • Click on that “+” icon
  • Drag it till the end of the table

All the dates for respective years will be filled in a sequential order, as shown above.

Conclusion

That’s all about using the Autofill feature in Google Sheets to fill the days, weeks, months, and years.

If you are still stuck somewhere or are having any particular issue, then feel free to comment below.

For more such Google Sheets tips and tricks, make sure to check our blog. It contains plenty of articles to organize and analyze datasets by date in Google Sheets.