Change Time Format in Excel & Google Sheets

This tutorial will demonstrate how to change time formats in Excel and Google Sheets.

Change Date Format Main

Excel Time Format

In spreadsheets, times are stored as decimal values where each 1/24th represents one hour of a day (Note: Dates are stored as whole numbers, adding a decimal value to a date number will create a time associated with a specific date). When you type a time into a cell, the time is converted to it’s corresponding decimal value and the number format is changed to time.

times stored in excel

After a time is entered in Excel as a time, there are several ways to change the formatting:

Change Time Formats

Time Format

The Ribbon Home > Number menu allows you to change a time to the default Time Format:

Time Format

Format Cells Menu – Time

The Format Cells Menu gives you numerous preset time formats:

Select Time Format

 

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:

number format button

Custom Number Formatting

The Custom section of the Format Cells Menu allows you the ability to create your own number formats:

Time Custom Format

 

To set custom number formatting for times, you’ll need to specify how to display hours, minutes, and/or seconds. Use this table as guide:

Time Format table

You can use the above examples to only display hours, minutes, or seconds. Or you can combine them to form a complete time:

Time format result

Times Stored as Text

Time as Text

To store a time as text, type an apostrophe (‘) in front of the time as you’re typing it:

TIMEVALUE TIME Format

However, as long as the time is stored as text you’ll be unable to change the formatting like a normal time.

You can convert the time stored as text, back to a time using the TIMEVALUE or VALUE Functions:

TIMEVALUE Function

Text Function

The TEXT Function is a great way to display a time as text.  The TEXT Function allows you to display times in formats just like the Custom Number Formatting discussed previously.

Time text

You can combine the TEXT Function with a string of text like this:

Current Time

Google Sheets Date Formatting

Google Sheets makes formatting times slightly easier. With Google Sheets, you simply need to navigate to the ‘More date and time formats’:

date format google sheets

 

The menu looks like this:

Google Time format

 

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:

Google format table

Change time Format in Google Sheets

All of the above examples work exactly the same in Google Sheets as in Excel.

Change Date Format in Google Sheets All of the above examples work exactly the same in Google Sheets as in Excel.