Change Date Format in Excel & Google Sheets

This tutorial will demonstrate how to change date formats in Excel and Google Sheets.

 

Excel Date Format

In spreadsheets, dates are stored as serial numbers, each whole number represents an unique day. When you type a date into a cell, the date is converted to it’s corresponding serial number and the number format is changed to date.

After a date is entered in Excel as a date, there are several ways to change the formatting:

Change Date Formats

Short Date / Long Date

The Ribbon Home > Number menu allows you to change between Short Dates (default) and Long Dates:

Format Cells Menu – Date

The Format Cells Menu gives you numerous preset formats:

 

Notice that in the ‘Sample’ area you can see the impact the new number format will have on the active cell.

The Format Cells Menu can be accessed with the shortcut CTRL + 1 or by clicking this button:

Custom Number Formatting

The Custom section of the Format Cells Menu allows you the ability to create your own number formats:

To set custom number formatting for dates, you’ll need to specify how to display days, months, and / or years. Use this table as guide:

You can use the above examples to only display a day, month, or year. Or you can combine them:

Dates Stored as Text

Date as Text

To store a date as text, type an apostrophe (‘) in front of the date as you’re typing the date:

However, as long as the date is stored as text you’ll be unable to change the formatting like a normal date.

You can convert the date stored as text, back to a date using the DATEVALUE or VALUE Functions:

=DATEVALUE(B3)

Text Function

The TEXT Function is a great way to display a date as text.  The TEXT Function allows you to display dates in formats just like the Custom Number Formatting discussed previously.

=TEXT(B3,C3)

You can combine the TEXT Function with a string of text like this:

="Today's date is: "&TEXT(TODAY(),"m/d/yyyy")

Google Sheets Date Formatting

Google Sheets makes formatting dates slightly easier. With Google Sheets, you simply need to navigate to the ‘More date and time formats:

 

The menu looks like this:

 

You’ll be able to select from a wide range of presets. Or you can utilize the drop-downs on top to select your desired format: