If you insert dates or times in your Google Sheet, you aren’t stuck with the default date formats. You can use the word for a month instead of a number or add seconds to the time by creating custom formats.
You might have a spreadsheet you’re presenting and want a more attractive date format using complete words for months or days rather than numbers or abbreviations. Or maybe you include times in your sheet and need it detailed to the millisecond. The custom date and time formats in Google Sheets give you great flexibility.
Create a Custom Date Format
You can set up the custom format in a cell that already contains a date or in a blank cell you plan to use later.
Select the cell where you want the date and go to Format > Number > Custom Date and Time in the menu.
When the Custom Date and Time Formats window opens, you’ll see the editing area at the top with popular formats beneath. If you like one of the formats you see, simply select it and click “Apply.”
If you want to customize the date format, you can start with one of the formats from the list if you like. This gives you a jumpstart by placing those elements into the editing box at the top which you can then customize. Plus, you can remove any items you see in the editing box by clicking the arrow and selecting “Delete.”
To show you how to create a completely customized date format, we’ll start with a blank slate in the editing box and add the elements we want.
Click the arrow on the right side of the editing box. There are three Date options you can add for day, month, and year. Simply click to select the one(s) you want.
With the elements in the editing box, click the arrow to the right of each to select how you want it to display. As you can see, you have various options; this is where that flexibility comes in.
For example, for Month you can display the number with or without the leading zero. You can also use the full word, an abbreviation, or the first letter.
Once you make your selections, you can add things like spaces, slashes, or commas between the elements if you like. When you finish, click “Apply” to use that format in the cell.
Create a Custom Time Format
Creating a custom format for the time works the same way as the date. Head to Format > Number > Custom Date and Time in the menu to open the format window.
RELATED: How to Change and Create Custom Number Format in Google Sheets
To start with an existing format, select it at the bottom to place its elements in the editing box and remove those you don’t want to use.
To add elements, click the arrow on the right of the editing box. You’ll see even more options for Time than Date. You can add hours, minutes, seconds, milliseconds, and AM/PM.
Just like with the date, use the arrow to the right of an element to pick its format. For hours, minutes, and seconds, you can display the number with or without the leading zero. For milliseconds, you can choose the precision of the millisecond and for AM/PM, you can choose a shortened uppercase or lowercase letter or the full AM and PM.
Because time formats normally include colons between the elements, be sure to add those in the editing box. When you finish, click “Apply.”
Combine Date and Time Formats
If you want your cell to include the date and the time, you can combine the above into one format.
Simply select the elements, add any spaces or symbols between them, and click “Apply” to use the combination date and time format.
Reuse Custom Formats Elsewhere
Recent formats you create will display in the menu. Select Format > Number and you’ll see your three most recent ones near the bottom. Choose one to use it.
You can also use the Format Painter to copy the formatting from one cell to another.
Creating your own custom format for the date, time, or a combination of both is a good way to customize your spreadsheet. For more, take a look at how to use and customize a theme in Google Sheets.
