When you want to enter dates in your spreadsheet, you don’t have to dig around for your calendar to find the right ones. Here are a handful of ways to add dates automatically in Google Sheets, from the current date to a list of dates, to speed up your data entry.
Enter the Current Date
One of the easiest ways to add the current date is by using the TODAY function in Google Sheets. This not only populates today’s date but automatically updates it whenever you make a change in your sheet.
Select the cell where you want the current date and enter the formula below. Be sure to include the empty set of parentheses in the formula.
=TODAY()
If you want to add the current date without lifting your fingers from the keyboard, you can also use a Google Sheets keyboard shortcut. Press Ctrl + ; (semicolon) on Windows or Command + ; on Mac.
Want to include the time with the date? You can use the NOW function instead of TODAY to add the current time.
=NOW()
Alternatively, use the keyboard shortcut Ctrl + Alt + Shift + ; (semicolon) on Windows or Command + Option + Shift + ; on Mac.
Change the Current Date Recalculation
By default, Google Sheets recalculates the current date (and time) with each change you make to your sheet. However, you can adjust this if you would like. Keep in mind that you cannot disable the feature.
Select File -> Settings. When the settings window displays, go to the Calculation tab, choose Recalculation in the drop-down box at the top, and click Save settings.
Notice that you can choose to recalculate the TODAY and NOW functions, as well as a few others, based on changes to the sheet along with every minute or hour.
Fill in a Series of Dates
If you’re creating a schedule or setting up a calendar in Google Sheets, you may want to add a series of dates. Using the AutoFill feature, you can automatically fill in a list of dates, based on the day, month, or year.
Fill in Dates by Day
To fill cells in a column or row based on day, enter the first date you want to use. Then, grab the fill handle in the lower-right corner of the cell, and drag vertically for a column or horizontally for a row.
Release when you have the number of dates in your list that you need. You should see the dates populate and the day change with each entry.
Fill in Dates by Month
Populating the date based on month is similar to the method for day. The difference is that you’ll need to enter at least two dates to start so that Google Sheets can recognize the pattern.
Add the first two dates and select both cells. Grab the fill handle in the corner of the second date’s cell, and drag to fill your column or row.
When you release the drag, you’ll see your date list, which changes monthly.
Fill in Dates by Year
Using the AutoFill feature, you can also create a list of dates based on year.
Like the month option above, start by entering at least two dates. Select both date cells, and drag the fill handle across or down to fill your column or row.
Release to see your list of dates by year.
Tip: once you have added your dates, learn how to sort by date in Google Sheets.
Populate a Complete Date
If you have a Google Sheet where the parts of your date reside in separate cells, you can combine those cells to automatically create a complete date. For this, you’ll use the DATE function in Google Sheets.
The syntax for the formula is DATE(year, month, day), where all arguments should be in numeric form. For example, you must use 12 instead of December.
Select the cell where you want the complete date, and enter this formula, replacing the cell references with your own:
=DATE(A2,B2,C2)
Notes About the DATE Function
When you have invalid portions of a date, Google Sheets recalculates the formula to accommodate the mistake. For instance, if you have 13 for the month, you’ll see the complete date display as the month following 12, which is January of the next year.
Also, if you have decimals for any of the DATE function’s arguments, Google Sheets truncates the decimal. For example, if you have 2.75 for the month, you’ll see the date show the month as 2 for February.
Display a Date Picker
If you’re sharing your sheet and want to provide an easy way for everyone to enter a date, you can insert a date picker. This will display a pop-up calendar for you to choose the date and populate the cell.
Select the cell where you want the date picker, and choose Data -> Data validation from the menu. When the Data Validation Rules sidebar displays on the right, choose Add rule.
Select Is valid date in the Criteria drop-down list, and click Done.
To display the date picker, simply double-click the cell. You’ll notice the current date highlighted but can choose any date and use the arrows at the top to move between months.
Add a Quick Date
One more way to add a date automatically in Google Sheets is by using a Smart Chip. Smart Chips allow you to attach files, insert contact cards, and add dates by simply typing the @ (At) symbol and picking from a list.
For dates specifically, you can add the date for today, tomorrow, yesterday, or a date of your choice.
Go to the cell where you want the date, and enter @. Move down to the Dates section in the list that opens, and choose the date you want.
Alternatively, click the arrow next to Dates in the list to display only the available options. Select the date you want, or select Date to show a pop-up calendar.
Spreadsheet applications like Google Sheets give you helpful tools and features to speed up data entry. With this list, you can see that adding dates automatically falls right in line with that useful collection.
If you like using the date functions on our list, why not check out some other lesser-known Google Sheets functions that could come in handy?
Image credit: Pixabay. All screenshots by Sandy Writtenhouse.
Our latest tutorials delivered straight to your inbox
