Inserting and processing dates is one of the routine tasks you need to handle when working on spreadsheet-based apps. Google Sheets makes this easier to insert dates in your preferred format and process the data correctly. In this article, we will discuss how you can insert dates in Google Sheets and use various methods to change the date format. Also, learn how to quickly insert dates in Google Docs and Microsoft Word.
Inserting Dates in Google Sheets
You have multiple options like using functions, manually entering and use keyboard shortcuts for inserting dates in Google Sheets. However, the first thing you need to understand is the default date format you can use in Google Sheets.
- Open Google Sheets and go to “Format” menu.
- Select “Number” and check the “Date” option.
- It will show the default date format you can use.
If you type date in a different format, Google Sheets will read it as text instead of proper date. Therefore, make sure to use this format when using in functions and queries.
1. Auto Filling Date with Manual Insertion
- Click on the empty cell and enter a date.
- Drag the dot showing on the bottom right corner of the cell and drop where you want to stop the auto-fill. This will auto-fill the date in a sequential order.
2. Use TODAY Function
Another simple method to insert date in the Google Sheets is using TODAY function. Once the function is added, the Google Sheets will automatically update the date every day. So, you can use this to showcase the current date whenever you open the file.
- Go to any empty cell and type =TODAY().
- Press enter key and current date will automatically appear in the cell.
3. Use Date Function
Date function in Google Sheets allows you to insert a date based on year, month, and day values as arguments.
- Go to any cell and type =Date(year,month,day). For example, type =Date(2023,3,26).
- Press enter key and you will see the date in the default format appears.
4. Use Date Picker with Data Validation
With the “Data validation” feature, you can control what users can add to the specific range of cells. It will not only insert the date but also ensure the entered date is valid.
- Go to “Data” menu from top navigation and select “Data validation” option.
- You will see a “Data validation rules” sidebar appears and click on “Add rule” button.
- First select the “Range” of cells, then select “Is valid date” as your “Criteria” and finally click “Done” button.
- A date picker will appear in the first cell in the selected range and select your date from the picker.
- You will “Date is valid” message in the “Data validation rules” sidebar.
This is how you can force to choose the valid date in range of cells from date picker.
5. Insert Date with Shortcuts
Press the Ctrl + ; keys to insert current date in Google Sheets. This is an easy option instead of using TODAY function.
Changing Date Formats in Google Sheets
Apart from the default date format, Google Sheets offers a variety of date formats as given in the below table:
| Format | Example |
|---|---|
| Day/Month/Year | 26/03/2023 |
| Day-Month Abbreviated-Year | 26-Mar-2023 |
| Day Month Abbreviated Year | 26 Mar 2023 |
| Day Month Year | 26 March 2023 |
| Day/Month/Year short | 26/03/23 |
| Day/Month | 26/03 |
| Day-Month-Year | 26-03-2023 |
| Day-Month-Year short | 26-03-23 |
| Year-Month-Day | 2023-03-26 |
The available formats are based on your “Locale” setting and will change if you modify the location. To change your “Locale”, go to “File” menu and select “Settings”.
Select any other location you want to use and save your changes.
If you want to use custom date format other the listed options, go to “Format > Number > Custom date and time” menu.
Select your desired format from any of the available options and then click on “Apply”.
Using Custom Date Format
If you want to use a custom format other than the available ones, click Day, Month, or Year tab. Select “Delete” to remove the current format and select another format that you want to use. Press “Apply” button to use your personalized format.
You can also access “Custom date and time” by clicking on the 123 icon from the ribbon.
Use Query Function for Bulk Date Format Change
You can use the Query function in Google Sheets to change the data formats in bulk. The basic syntax of the function is =QUERY(data, query, [headers]) where:
- Data represents cells or sheet having data you want to query.
- The query is an SQL command for selection of data.
- Header is an optional part defining the number of header rows in the data set.
In the following example, there are 8 dates in DD-MM-YYYY format. Here, you can use the Query function as below to change the format to DD/MM/YYYY. You can replace this with your preferred format.
=QUERY(A3:A10,"SELECT * FORMAT A 'DD/MMM/YYYY'")
Type the formula in any empty cell and press enter to get the converted formats as below.
Query function is more powerful that you can use other SQL commands for sorting and grouping dates for processing.
Final Words
Google Sheets offers various types of date formats for you to choose the one that best suits your needs. You can choose any of the five methods explained above to easily insert the date in Google Sheets. If the default format is not useful, you can use custom option and Query function to create your desired date formats.
