How to Change a Date Format in a Google Spreadsheet


Office productivity suites were once exclusive -- and expensive -- software products controlled by major manufacturers. Open source and online products have broken that monopoly, and Google Sheets is a full-featured alternative that loads in your Web browser and saves work in Google Drive cloud storage, for access anywhere you can connect to the Internet. You can select date formats both globally and in rows, columns and individual cells.

Changing Your Locale

  • You can adjust the default date format in Sheets by changing your locale. For example, setting your location to the United States formats dates in the "month/day/year" format. If you prefer usingthe "year/month/day" order, switching your locale to Canada (English) changes the default. Change your locale by clicking "File" from the menu bar, then clicking "Spreadsheet settings" from the drop-down menu. Click on the box under "Locale:" and choose the country with the appropriate date format. This will change other location-based defaults -- such as currency, for example -- but it will not change your language preference.

Entering Date Information

  • Once your default date format is chosen, Sheets attempts to convert numbers entered as dates to that format. For example, with United States chosen as locale, entering "8-31" into a cell returns "8/31/2014" as a date format. Entering "31-8" returns "31-8," however, since Sheets assumes a non-date, non-numerical entry. Attempting to enforce date formatting using the number icon from the toolbar ribbon set a cell to a date format, but does not convert "31-8" to a date, since Sheets still expects the month to appear first.

Using Custom Formats

  • Once date data is entered successfully, you can alter how it appears in a cell by choosing a custom format. Select the cell or range of cells to format, then click the number formatting icon from the toolbar ribbon. Hover over "More Formats," then click "More date and time formats." You can select a predefined format from the list, or you can use the tool at the top of the window to select the format for each element of the date.

Formatting Tips and Tricks

  • With your date entered and formatted in one cell, you can quickly add a range of dates in the same format by clicking and dragging the box in the lower right corner of the original cell. This works in both columns and rows. Conditional formatting is another useful tool that can apply to date functions. Right click the cell or range of cells and click "Conditional formatting" from the drop-down menu. You can create rules to color cells based on date information. This may be handy as a visual cue of upcoming deadlines in a project tracking spreadsheet, for example.

Related Searches


  • Photo Credit Daniilantiq/iStock/Getty Images
Promoted By Zergnet


You May Also Like

Related Searches

Check It Out

Geek Vs Geek: Robot battles, hoverboard drag race, and more

Is DIY in your DNA? Become part of our maker community.
Submit Your Work!