Do you need to add a date to your spreadsheet? This post is going to show you how to insert a date value in Google Sheets.

Dates are one of the most common data types as they help you track time. That’s why you will find a date column in almost all datasets you come across.

When you want to add a date column or insert dates in a dataset, there are a couple of methods you can follow.

This article will take you through the following three methods that you can accomplish this task.

• Manually insert dates with Formatting.
• Insert dates with Data Validation.
• Insert dates with the DATE function.

Get you copy of the example workbook used in this post and follow along.

Insert a Date Manually with Formatting

The simplest way to insert a date is just to go ahead and manually write the date into an empty cell.

As you write the date, you must make sure that you follow the date format as specified in your locale setting, or else the spreadsheet wouldn’t identify the item as a date.

Dates will generally have three popular formatting styles in which they’re written.

• DD/MM/YYYY as in 31 October 1990.
• MM/DD/YYYY as in October 31, 1990.
• YYYY/MM/DD as in 1990 December 31.

The type of formatting you must use when writing a date will depend on your spreadsheet’s locale setting.

You can change the Locale setting of your spreadsheet from the Settings option in the File menu.

You can change the Locale and Time zone settings using the dropdown menus. When you make a change, you will have to click on the Save and reload option.

When you use the US Locale, the accepted date formats are MM-DD-YYYY and YYYY-MM-DD. This applies whether you write the date in words or as numbers.

If you write the date in words, the spreadsheet will automatically change it to the accepted format.

But when you write it in numbers, the spreadsheet will not recognize it as a date, as shown in the image above.

Insert a Date with Data Validation

Another way you can insert a date is by using data validation.

With data validation, you can strictly control the type of data that goes into a cell. You can specify whether a cell only takes a text data type, a number, or a date.

You can also set it up to reject inputs that don’t follow the controls already set up.

Data validation essentially helps you to reduce the instances of having data entry errors to maintain the integrity of your data.

In this case, you will set up data validation on the date column and all you will have to do is double-click on a cell in the column to select a date.

Go to the Data menu and select Data validation.

Follow these steps in the Data validation dialogue menu.

1. Select the cell range to which you want to apply the validation from the Cell range section.

When you preselect the range before opening the Data validation menu, it gets automatically updated in the Cell range section.

2. Select Date from the first drop-down menu and is a valid date from the second drop-down menu in the Criteria section.

3. Select the Reject input option in the on invalid data section. This is where you choose what happens with data that doesn’t conform with the set Criteria section.

4. Check the Appearance option and some text to the input such as Enter a valid date. This is what will display when invalid input is made.

This is very handy when sharing the workbook. You can share a message about the required data format in the text box provided.

5. Click on the Save button.

Now, you just have to double-click on the cells in the Date column and a calendar pops up where you can select the date you want to use.

Insert a Date with the DATE Function

The last method that will be discussed is the DATE function.

This is very useful especially when you have the date components in individual columns, and you want to put them together into one date value.

= DATE ( year, month, day )

The DATE function combines the year, month, and day into a single date value.

• year is the year value of the date you want to return.
• month is the month value of the date you want to return.
• day is the day value of the date you want to return.

= DATE ( B3, C3, D3 )

The above formula will combine the Year, Month, and Day columns and return the corresponding date!

Conclusions

Whether you want to create a date column or fill in a missing date, these methods can easily help you navigate this task.

You can choose to manually enter the date when only a couple of dates are missing or use data validation when you want to create a date column in a new dataset.

In the case where your dataset already has the day, month, and year components, the DATE function can easily help you combine them all into one column.

Are there any other methods for inserting dates that you use? Let us know in the comments!