Understanding Number Formats

Date formats

Whenever you're working with dates, you'll want to use a date format to tell the spreadsheet that you're referring to specific calendar dates, such as July 15, 2016. Date formats also allow you to work with a powerful set of date functions that use time and date information to calculate an answer.

Spreadsheets don't understand information the same way a person would. For instance, if you type October into a cell, the spreadsheet won't know you're entering a date so it will treat it like any other text. Instead, when you enter a date, you'll need to use a specific format your spreadsheet understands, such as month/day/year (or day/month/year depending on which country you're in). In the example below, we'll type 10/12/2016 for October 12, 2016. Our spreadsheet will then automatically apply the date number format for the cell.

Entering a date in the spreadsheet

Now that we have our date correctly formatted, we can do different things with this data. For example, we could use the fill handle to continue the dates through the column, so a different day appears in each cell:

Using the fill handle with the date format

If the date formatting isn't applied automatically, it means the spreadsheet did not understand the data you entered. In the example below, we've typed March 15th. The spreadsheet did not understand that we were referring to a date, so the automatic format is treating this cell as text.

The spreadsheet doesn't recognize this as a date

On the other hand, if we type March 15 (without the "th"), the spreadsheet will recognize it as a date. Because it doesn't include a year, the spreadsheet will automatically add the current year so the date will have all of the necessary information. We could also type the date several other ways, such as 3/15, 3/15/2016, or March 15 2016, and the spreadsheet would still recognize it as a date.

To check if Google Sheets recognizes your entry as a date, look in the formula bar. The value of the cell in the formula bar will be converted to a numeric format like 3/15/2016 but will display in the sheet in the format which you originally entered.

Checking the date format in the formula bar


Try entering the dates below into a spreadsheet and see if the date format is applied automatically:

  • 10/12
  • October
  • October 12
  • October 2016
  • 10/12/2016
  • October 12, 2016
  • 2016
  • October 12th

Other date-formatting options

To access other date-formatting options, select the More formats drop-down menu on the toolbar and choose More Formats at the bottom, then select More date and time formats.

More date and time formats

The Custom date and time formats dialog box will appear. From here, you can choose the desired date-formatting option. These are options to display the date differently, like including the day of the week or omitting the year.

Custom date and time formats

As you can see in the formula bar, a custom date format doesn't change the actual date in our cell—it just changes the way it's displayed.

The new date format