One doesn’t need to worry about Microsoft’s expensive subscription charges for Office 365, since Google offers a free alternative, Google Sheets. Just like Excel, there are tons of features waiting to be discovered. When we were compiling a daily report, we found numerous ways to add dates automatically on a Google Sheets document.
There are three ways to add dates to Google Sheets, these include auto-filling cells, using the date function, formatting the cells, or inserting the date shortcut. We have illustrated these methods using a web browser on a Windows PC, but they work on Mac, iPhone, and Android as well.
Using these methods, you can automate a few repetitive tasks that involve adding dates to cells and improve your efficiency. Let’s discover them all.
Tip: Date and time formats depend on the default locale of your Google Sheets. Here’s how to change it: open Google Sheets > go to File > settings. In the pop-up window, go to the General tab > select your region under the Locale drop-down menu.
Automatically Enter the 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 type the below function.
=TODAY()Step 2: Now, hit the enter key.
There you 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 us look at how we can automatically enter dates in chronological order in Google Sheets.
How to Autofill Dates in Google Sheets
Autofill is a feature on Google Sheets that helps you fill the cells in series with data based on values present in a few cells. For example, if I enter a date in Cell A1, I can fill the entire column A with dates, since Google Sheets recognizes that there’s a date in A1 and when prompted to autofill the rest of the column, it fills it with increasing order of dates.
However in case, you want to check the date and time format before you enter a date, here’s how to do it.
We can autofill days, weeks, and months in Google Sheets. Let’s see how we can do that.
Autofill Days in a Column in Google Sheets
Step 1: Open Google Sheets and enter a 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 dates for.
As you can see, the dates are auto-filled for all the selected cells in a column in the same format.
Xem thêm : Some Cool Tips and Apps for Multitasking on Android
That is how you can autofill consecutive days in Google Sheets. Next, let us see how to autofill dates that are a week apart.
Autofill Weeks in a Column on Google Sheets
Here’s how you can autofill dates in a column in Google Sheets that are a week apart.
Step 1: Open a Google Sheet and enter two dates that are a week apart in the 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 that are a week apart.
Similarly, we can autofill dates in a column that are a month apart. Here’s how.
Autofill Months in a Column on Google Sheets
Step 1: Open Google Sheets and enter two dates that are a 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-filled with dates that are a few months apart.
Next, let us take a look at how we can enter weekdays only on Google Sheets.
Autofill Weekdays Only on Google Sheets
If you are using Google Sheets at work, you seldom need to enter data for a non-working day or a weekend. But in the previous method to autofill dates, weekends were also auto-filled. If you are working on a data-heavy spreadsheet, it is time-consuming to manually delete weekends.
Therefore, you can autofill dates excluding the weekends – Saturdays and Sundays. Here’s how.
Xem thêm : How to Factory Reset iPhone 13 with or without Passcode
Step 1: Open Google Sheets and enter a date in a cell that you want to start with.
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 cell is automatically filled with the next working day.
Step 4: Click on the bottom right corner of the cell, and drag it down to the cell that you want to fill. The formula governs the pattern, and will only enter working days and exclude Saturdays and Sundays.
As you can see in the image below, the cells contain only working days, and Saturdays and Sundays are excluded.
That is everything you need to know on how to autofill dates in Google Sheets. If you are looking for a way to modify and change the format of dates on Google Sheets, you can take a look at the article we recently published.
Next, let us see how we can merge date and time on Google Sheets. This will be helpful if you have two columns: one for the date and another for the time.
Bonus: Merge Date and Timestamp on Google Sheets
When you have a column with a date and a time that corresponds to it in a column, it makes sense to merge them into a single column and minimize the redundancy. Here’s how to do it.
Step 1: Open Google Sheets and enter the below formula that helps you merge date and time functions on Google Sheets into a text.
Make sure you enter the right cell locations for the date and time respectively. In our case, it is 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 a single cell as plain text. You can select and drag the merged cells to autofill the entire column if you have multiple separate date and time cells.
There you go, that is everything you need to know on how to autofill dates on Google Sheets. However, if you have any more questions regarding the same, you can take a look at the FAQ section below.
FAQs on Autofilling Dates on Google Sheets
Get Your Dates Sorted
We hope this article helped you automatically add dates on Google Sheets with ease. Features like these have saved us a lot of time for us and considerably increased our efficiency in a spreadsheet. Therefore, we couldn’t help but share it with you. Similarly, you can also explore another article that helped us do the same: A guide to using conditional formatting in Google Sheets.
Nguồn: https://eireview.org
Danh mục: Technology