Change Time Format in Excel & Google Sheets

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

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.

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:

Format Cells Menu – Time

The Format Cells Menu gives you numerous preset time formats:

 

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:

Custom Number Formatting

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

 

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:

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

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:

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(B3)

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.

=TEXT(B3,C3)

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

="Current time is: "&TEXT(NOW(),"h:mm:ss AM/PM")

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’:

The menu looks like this:

 

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:

In Google Sheets your formatted time will look like this: