Change Date Format in Excel & Google Sheets

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

Change-Date Format Main Function

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.

date format excel

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:

short date long date

Format Cells Menu – Date

The Format Cells Menu gives you numerous preset formats:

dates format excel

 

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:

number format button

Custom Number Formatting

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

custom number formats excel

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:

DMY Format

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

Date Format

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:

DATEVALUE Store as text

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 Function

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

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

Today Text

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:

date format google sheets

 

The menu looks like this:

custom date time formats google sheets

 

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:

custom month format google sheets

Change Date Format in Google Sheets

All of the above examples work exactly the same in Google Sheets as in Excel.

Date Format Google sheet