How To Format Date In Google Sheets : Simple Step 2022

Written by Amy Brink Technically reviewed by Channa S Bandara.
Publish Date : April 19, 2021 | Review Date : April 21, 2021
Google Sheet, Spread Sheet
How To Format Date In Google Sheets

Google sheet now become one of the most popular data management tools in the industry. it’s now more advance and improves collaborating working features to deal with the same sheet among many people. Date & time is one of the key features in google sheet & it has a lot of features and features as well. in this tutorial we cover how to format date in google sheets in google sheet using the inbuilt google option and custom data validation. 

The basic date formula mostly using to format date in google sheets

In google sheet contain over 20 ways to define format date in google sheets. this is most common usage of date format in google sheets

  1. DATE(year, month, day):  this is the primary date function/date format in the google sheet. This ate need to be Number w& separate by a comma,
    =DATE(1969,7,20)
    =DATE(A2,B2,C2)    Inputs to DATE must be numbers & Year (yyyy) contain a four-digit number. month (mm) & day (dd) contain two-digit number.
  2. DAY(date):  this is the primary date function in the google sheet. This ate need to be Number w& separate by a comma,
    =DAY(DATE(1969,7,20))
    =DAY(A2)
    =DAY(40909)
    =DAY("7/20/1969")
    This is will important when you need to define the day in date on cell reference.
  3. TODAY() : TODAY provide day without time format in the cell reference. But remember that this function can hurt your spreadsheet performance. Because it always needs to verify the current date in the spreadsheet.
    =TODAY()
  4. NOW(): this can be used to get the current date and time as a data value into a spreadsheet. But make sure that this function really hurt for spreadsheet performance same as TODAY() function.
    =NOW()
  5. DAYS(end_date,start_date): this function can be used to validate the date difference between two dates.
    =DAYS("7/24/1969", "7/16/1969")

How to insert date picker/calendar in google sheets?

Instead of using manual date entering, google sheet offers a special data validation feature for date popup which can be insert date without manual entering. it extremely easy to format date in google sheets.

  1. Open a spreadsheet in Google Sheets.
  2. Highlight cell or column you’d like to add a date picker.Highlight cell or column you’d like to add a date picker
  3. Then move your mouse pointer to the top of navigation and click Data ( middle of Format and Tool navigation link ) Then Select Data > DataValidation. hen Select Data - Data Validation
  4. Then Select the Date option from the criteria dropdown. It only enables date related insert.Then Select the Date option from the criteria dropdown. It a covert all data into date format
  5. The second dropdown will represent how to date format needs organize in your selected cell or column. I select is a valid date. Please read below full detailed instructions on these option usages.
  6. The second dropdown will represent how to date format needs organise in your selected cell or column. I select is a valid date. Please read below full detailed instruction on these option usage
  7.  
  8. Then click the save button to apply this date validation to your selected area.
  9. Then double click the cell (which you already apply data validation) & the date picker will pop up into the relevant cell. Then double click cell ( which you already apply data validation) & the date picker will popup into the relevant cell

Feature of date criteria in data validation (to format date in google sheets).

Data validation is represented a set of criteria parameter that implements with cell reference without any formula. In data validation set up certain parameter to deal with the data operation.


  1. Is valid date: this represents the valid date format that you defined in the cell of default date format in your google sheets.
  2. Equal to: mention the exact date in cell & define violate its validation rule message if someone enters the date which not define.
  3. Before: it ensures that only can enter before days which define on the criteria date. The invalid insert can be rejected or validate with an error message
  4. On or Before :it same as before operation additionally, it can insert the defined date on criteria date parameter
  5. After: it ensures that only can enter after days which define on criteria date. The invalid insert can be rejected or validate with an error message
  6. On or After: it same like after logic also can insert the defined date on criteria date parameter.
  7. Between: only can date selection between two given dates in a cell in google sheet.
  8. Not Between: ignore date selection between two given dates in a cell in google sheet.

How to Change the default date format in Google Sheets?

Most of the time you may need to deal with multiple region base project or data. You have to change the date and time base on that region format. There is 2 way you can implement date format setting into spreadsheets.

#Way 01: change local settings

You can change the spreadsheet location setting. but it not recommended way if you working with multiple region datasheets.

#Way 02: define the custom date and time formats

This is recommended way to define the date and time value that is required in a parsing operation. Let’s check how t implement it with google sheet.

  1. Open a spreadsheet in Google Sheets.
  2. Highlight cell or column you’d like to add a date picker, or which already add. Don’t worry about the column header (it will automatically reject).
  3. Then move your mouse pointer to the top of navigation and click Data ( middle of Format and Tool navigation link ) Then Select Format > Number > More Formats >  More date and time formats.Then move your mouse pointer to the top of navigation and click Data ( middle of Format and Tool navigation link ) Then Select Format > Number > More Formats > More date and time formats
  4. Then you can get custom date formats pre-define dialog box. You can scroll to define date preset on the option box or you can create a custom date format using the custom date set upfield.
    For this example, I convert my current date format to Australian date format ( DD/M/YYYY = 14 March 2016 )
    1. Click the currently selected date, month year in the setting field and click the delete button to remove the existing selected parameter.remove the existing selected parameter
    2. Remove separator symbol if not use (select and press delete/backspace button)
    3. Click the arrow down icon on the right side in the setting field. arrow down icon on the right side in the setting field
    4. Select year, month, day base on your date format & insert a suitable separator symbol after all Click apply button.
    5. your custom date format will update all selected date records.

By default, the Google Sheet date start  1/1/1900. Because google sheets use the default date system as 1900.

Conclusion

Google sheets are one great tool for organize, edit, analysis variant type of information. Date in one of the important data references most of the time using in the spreadsheet. Use need an easy way to operate ( without query writing ) in the spreadsheet. Today we discussed what type of date formatting features/formulas which you can easily implement with google spreadsheets.

Table of article :