Download the example workbook
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
Easy Excel Automation
Add Excel automation to your workbook with just a few clicks.
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.
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.
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: