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.
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.”
You can choose from a wide variety of preset formats on the Number tab, in the Format Cells Dialoge box . If the format you want to use isn’t listed, you can create your own using the catagory Custom.
~ The secret is to know the code. ~
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.
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.
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.
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.
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. Use the Contact form (below) or give us a call to get your training scheduled. Diana also trains for Washoe County and UNR Extended Studies.
We also have 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.