One need not worry about Microsoft’s expensive subscription fees to Office 365, as Google offers a free alternative, Google Sheets. Just like Excel, there are many features waiting for you to discover. When composing our daily reports, we found many ways to automatically add dates to Google Sheets documents.
There are three ways to add dates to Google Sheets, including autofilling cells, using the date function, formatting cells, or inserting a date shortcut. We demonstrated these methods using a web browser on a Windows PC, but they also work on Mac, iPhone, and Android.
Using these methods, you can automate some of the repetitive tasks involved in adding dates to cells and improve your efficiency. Let’s explore all of them.
Tip: The date and time format depends on your Google Sheets default language. Here’s how to change it: open Google Sheets > go to File > settings. In the pop-up, go to the General tab > select your region in the Language drop-down menu.
1. Automatically enter current date on Google Sheets
Let’s start by automatically entering today’s date using the Google Sheets date function called TODAY(). This returns today’s date in a cell. Here’s how to use it.
Step 1: Open Google Sheets, place your cursor in a cell and enter the function below.
=TODAY()
Step 2: Now, press enter key.
Your go. You can now see today’s date in a cell on Google Sheets.
Alternatively you can also use the key combination ‘Ctrl +;‘ on a cell in Google Sheets and it will return the current date.
Next, let’s see how we can automatically enter dates in chronological order in Google Sheets.
2. How to AutoFill Dates in Google Sheets
Autofill is a feature on Google Sheets that helps you populate cells in series based on values contained in several cells. For example, if I enter a date in Cell A1, I can fill the entire column A with the date, because Google Sheets recognizes that there is a date in A1, and when prompted to autofill the rest of the column, it will fill in ascending order of date.
However, in case, you want to check the date and time format before entering the date, here is how.
We can autofill days, weeks, and months in Google Sheets. Let’s see how we can do that.
1. Autofill dates in a column in Google Sheets
Step 1: Open Google Sheets and enter the date in a cell.
Step 2: Click and hold on the bottom right corner of the cell and drag it down to the cell you want to autofill with the date.
As you can see, the date is auto-populated for all selected cells in a column with the same format.
That’s how you can autofill consecutive dates in Google Sheets. Next, let’s see how to autofill dates one week apart.
2. Autofill week numbers in a column on Google Sheets
Here’s how you can autofill dates in a column in Google Sheets one week apart.
Step 1: Open Google Sheets and enter two dates a week apart in consecutive cells.
Step 2: Select and highlight the cells.
Step 3: Click on the bottom right corner of the highlighted cells and drag it until the cell you want to autofill.
As you can see, the column is filled with dates one week apart.
Similarly, we can autofill the date in the column one month apart. This is the way.
3. Autofill month in a column on Google Sheets
Step 1: Open Google Sheets and enter two dates one month apart in consecutive cells in the column.
Step 2: Select and highlight the cells.
Step 3: Click on the bottom right corner of the highlighted cells and drag it until the cell you want to autofill.
The column is auto-populated with dates several months apart.
Next, let’s see how we can only enter the days of the week on Google Sheets.
3. Autofill days of the week Google Sheets Only
If you’re using Google Sheets at work, you rarely need to enter data on non-working days or weekends. But in the previous autofill method, weekends are also autofilled. If you are working on a spreadsheet with a lot of data, manually deleting weekends will be very time consuming.
Therefore, you can auto-populate dates excluding weekends – Saturday and Sunday. This is the way.
Step 1: Open Google Sheets and enter the date in the cell where you want to start.
Step 2: Enter the formula in the cell below. Make sure you enter the correct column in the formula. (column A in our case)
=WORKDAY.INTL($A$2, row(A1),1)
Step 3: The next box will automatically be filled with the next business day.
Step 4: Click on the bottom right corner of the cell and drag it down to the cell you want to fill. The formula governs the sample and will only enter business days and exclude Saturdays and Sundays.
As you can see in the image below, cells containing only business days and Saturday and Sunday are excluded.
That’s all you need to know about autofilling dates in Google Sheets. If you are looking for a way to edit and change the date format on Google Sheets, you can check out the article we just published.
Next, let’s see how to merge dates and times on Google Sheets. This will be useful if you have two columns: one for date and one for time.
Bonus: Merge dates and timestamps on Google Sheets
When you have a column with the date and time corresponding to it in one column, you should merge them into a single column and minimize redundancy. Here’s how to do it.
Step 1: Open Google Sheets and enter the formula below to combine the date and time function on Google Sheets into the text.
Make sure you enter the correct cell location for the respective date and time. In our case, it’s A2 and B2.
=TEXT(A2,”m/dd/yy”)&TEXT(B2,”hh:mm:ss”)
Step 2: As you can see, the date and time are merged into one cell as plain text. You can select and drag merged cells to autofill entire columns if you have multiple separate date and time cells.
That’s it, that’s everything you need to know about how to autofill dates on Google Sheets. However, if you have any further questions regarding this, you can check out the FAQ section below.
Google Sheets Auto Date FAQ
1. Can you autofill dates in multiple formats on Google Sheets?
You can only autofill dates in a single format.
2. Can you autofill dates one year apart on Google Sheets?
Correct. Enter two dates one year apart, then select the cells and drag them down to autofill.
Organize your day
We hope this article helped you to automatically add dates on Google Sheets easily. Features like these have saved us a lot of time and greatly increased our efficiency in spreadsheets. So we couldn’t help but share it with you. Similarly, you can also explore another article that helped us do the same: Guide to using conditional formatting in Google Sheets
Categories: How to
Source: thpttranhungdao.edu.vn/en/
