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.

Select Format, Number, Custom Date and Time

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.”

Custom Date and Time Formats window

Advertisement

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.”

Delete an element

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.

Date options

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.

Advertisement

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.

Date elements

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.

Time options

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.

Time elements

Because time formats normally include colons between the elements, be sure to add those in the editing box. When you finish, click “Apply.”

Custom time format with colons

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.

Advertisement

Simply select the elements, add any spaces or symbols between them, and click “Apply” to use the combination date and time format.

Date and time combination 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.

Recent formats

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.

Profile Photo for Sandy Writtenhouse Sandy Writtenhouse
With her B.S. in Information Technology, Sandy worked for many years in the IT industry as a Project Manager, Department Manager, and PMO Lead. She learned how technology can enrich both professional and personal lives by using the right tools. And, she has shared those suggestions and how-tos on many websites over time. With thousands of articles under her belt, Sandy strives to help others use technology to their advantage.
Read Full Bio »