Custom date formats are often useful in your reports. Excel has two quick formats for a date, one is “Short Date” the other is “Long date.”

Google Sheets has a simple “Date” format which looks just like Microsoft Excel’s Short Date. Hopefully, by the time you finish this post, you’ll be able to create a date format to match your specific needs.

No Joke

I selected April 1, 2020 to use in the example, because why not? April is a signle digit month, and the 1st is also a single digit, you’ll see where this matters.

When cells are formatted with a specific date format, such as a short date, Excel (and Sheets) will display 4/1/2020 no mater how you enter the date. Type in 4-1, or 4/1, or April 1, each date will display as the set format. This is important for consistency. If the same cell was formatted as a “Long date,” Excel would display “Wednesday, April 1, 2020.”

Custom Formats

Any Date entered (regardless of format) will always include the current year (unless of course you enter a specific year). Even if the year doesn’t display on the sheet due to the format, when you enter a month and date, the current year is saved with the date. You will see it in the Formula bar.

The Secret Code

To create a custom format for your date, in Excel you need to open the Format Cells Dialog box. This can be done in a number of methods. this screenshot shows you the right-click method.

Remember! You want to have the desired cells selected. In the Format Cells Dialog box you’ll need to be on the Number tab, and select the Category Custom from the bottom of the list.

Month Format

m represents the month
m will display a single digit month, such as 4 for April. (double digits, like October, will display two digits)
mm will display a double digit month, such as 04 for April.
mmm will display the three character month, such as Apr for April.
mmmm will display the full name of month, such as April.

Date Format

d represents the date
Similarly, use d for the date.
d will display a single digit date, such as 1 for the first. (again, double digit dates such as the 11th, would display two digits)
dd will display a double digit date, such as 01 for the first.
ddd will display the three character abbreviation for the day of week, such as Wed for Wednesday.
dddd will display the full name of day of week, such as Wednesday.

You can probably guess what represents the year.

Year Format

y represents the year
Years have fewer options.
yy is a two digit year, such as 20.
yyyy is a four digit year, such as 2020

Use common puctuation (comma, dash, slash and spaces) to seperate the date, month and year.

Format Samples

Below the formatted cell (in the image below), you can see what the format looks like in the Excel Format Cells Dialog box, under the category Custom.

Note: In the formula bar, the date (except the function) is displayed as a short date, including the year.

Google Sheets Format

Google Sheets uses the same code as Microsoft Excel. To get to the Format number options, click Format → Number → More formats → Custom number format

As always, we’re available to help you with your individual questions. With over twenty years of experience, we are proud to have clients in many industries including: Real Estate, construction, manufacturing, non-profits, and research and development.

You can reach us by calling or texting 775 . 525 . 0885 or emailing info [at] MyRenoComputerTutor [dot] com

We’re dedicated to helping people understand what they’re doing. So please reach out. You can contact us via email, or voicemail, and you can even text us to get your training scheduled. Diana also trains for Washoe County, City of Reno and UNR Extended Studies.

There are other posts about using Excel, including posts on absolute vs relative references, using proper Order of Operations, and grouping dates in PivotTables. Check out what you can do with VLOOKUP or the newest XLOOKUP function.